[SQL] 如何快速搜尋整個 SQL Server 資料庫中所有表格所有欄位中的所有資料

 

https://blog.miniasp.com/post/2010/07/12/Search-all-columns-of-all-tables-in-a-database-for-a-keyword

最近在研究 TFS2010 的東西，不過這幾天慢慢的後悔安裝 TFS2010 繁體中文版，有些翻譯實在讓我太痛苦了，因為我現在看的資料都以英文為主，但當操作介面翻譯成中文後有時卻難以對應出影片或文章中講的項目到底在我的 Team Explorer 中到底是什麼項目，因此設法搜尋資料庫中上百張表格的所有資料，看看到底有沒有可以修改的地方。

如上圖示「使用者本文」是什麼？「產品計畫」又代表什麼呢？由於手邊沒有英文版的 TFS2010 可供參考，所以只好在看國外影片時特別注意翻譯然後自己要設法記得才行，但其他人怎麼辦？一個一個教嗎？

因為這個需求讓我想到利用 T-SQL 看可否搜尋整個資料庫，看是否有可以修改資料庫中的資料就自動改變介面顯示的詞彙，然後我就找到一個由 Narayana Vyas Kondreddi 寫好的版本可以很方便的搜尋整個資料庫中所有表格所有欄位的資料，非常方便，我修改成不建立預儲程序(Stored Procedure)就能執行的的版本如下：

DECLARE @SearchStr NVARCHAR(200) = N'.png' 

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM   INFORMATION_SCHEMA.TABLES
        WHERE  TABLE_TYPE = 'BASE TABLE'
           AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
           AND OBJECTPROPERTY(
                 OBJECT_ID(
                  QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
                  'IsMSShipped') = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE    TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND  TABLE_NAME    = PARSENAME(@TableName, 1)
                AND  DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND  QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                '
                SELECT ''' + @TableName + '.' + @ColumnName + ''', 
                        LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' + '
                WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT * FROM #Results

DROP TABLE #Results

使用方法很簡單，兩個步驟：

1. 先開啟 Management Studio 並連線至 TFS 使用的資料庫，並切換至該專案集合資料庫

   

2. 修改上述 T-SQL 的第一行，把要搜尋的關鍵字換成你想搜尋的字串再按下執行即可得到搜尋結果

   

找到了這些終於可以讓我好好的來「英文化」這些怪異的翻譯了。

以下是幾個可參考的「中翻英」範例：

• 產品計畫 Product Planning
• 使用者本文 User Story
• 產品待處理項目 Issues
• 我的測試案例 My Test Case
• 我的工作 My Tasks
• 我的 Bug My Bug

謎之聲：Product Backlog 到哪去了？

底下是更新翻譯文字的 T-SQL 指令碼：

UPDATE dbo.QueryItems
SET Name=REPLACE(Name, '使用者本文', '使用者故事(User Stories)');

UPDATE dbo.QueryItems
SET Name=REPLACE(Name, '產品待處理項目', '產品待處理項目(Issues)');

UPDATE dbo.QueryItems
SET Name=REPLACE(Name, '產品計劃', '產品計劃(Product Backlog)');

相關連結

• How to search all columns of all tables in a database for a keyword?