SQLServer反向搜索字串在哪個表中或視圖中

sarchdb.sql

-- 宣告搜尋的字串和結果表格
DECLARE @TargetString nvarchar(255) = '<要檢索的字串>';
DECLARE @SearchResults TABLE (
  TableType nvarchar(10),
  FullTableName nvarchar(500),
  ColumnName nvarchar(370),
  ColumnValue nvarchar(3630)
);

-- 設定交易隔離等級為 READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 宣告用於處理的變數
DECLARE 
  @CurrentSchema nvarchar(128),
  @CurrentTableName nvarchar(128),
  @CurrentColumn nvarchar(128),
  @QueryString nvarchar(110),
  @CurrentTableType nvarchar(10),
  @DatabaseName nvarchar(128),
  @TotalTables int,
  @CurrentTableNumber int,
  @FullTableName nvarchar(256)

-- 初始設定
SET @QueryString = QUOTENAME('%' + @TargetString + '%','''')
SET @DatabaseName = QUOTENAME(DB_NAME())
SET @CurrentTableNumber = 0

-- 計算總表格數
SELECT @TotalTables = COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
  AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

-- 宣告和開啟 Cursor
DECLARE table_cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
  AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
ORDER BY TABLE_SCHEMA, TABLE_NAME;

OPEN table_cursor;

-- 取得第一筆資料
FETCH NEXT FROM table_cursor INTO @CurrentSchema, @CurrentTableName, @CurrentTableType;

-- 開始處理每個表格
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @CurrentTableNumber = @CurrentTableNumber + 1
  SET @FullTableName = QUOTENAME(@CurrentSchema) + '.' + QUOTENAME(@CurrentTableName)

  -- 檢查表格或視圖是否存在
  IF EXISTS (
    SELECT 1 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(@FullTableName)
      AND (type = 'U' OR type = 'V')
  )
  BEGIN
    -- 打印當前搜尋的表格或視圖名稱
    PRINT 'Searching ' + @DatabaseName + '.' + @FullTableName + ' [' + 
          CAST(@CurrentTableNumber AS varchar) + '/' + CAST(@TotalTables AS varchar) + 
          '] (Type: ' + @CurrentTableType + ')';

    -- 迭代當前表格/視圖的所有列
    SET @CurrentColumn = ''

    WHILE 1 = 1
    BEGIN
      -- 獲取下一個列名稱
      SELECT @CurrentColumn = MIN(QUOTENAME(COLUMN_NAME))
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = @CurrentSchema
        AND TABLE_NAME = @CurrentTableName
        AND QUOTENAME(COLUMN_NAME) > @CurrentColumn

      -- 如果沒有更多的列，則跳出迴圈
      IF @CurrentColumn IS NULL BREAK

      -- 搜尋目標字串
      BEGIN TRY
        SET NOCOUNT ON;
        INSERT INTO @SearchResults
        EXEC ('
          SELECT
            ''' + @CurrentTableType + ''', 
            ''' + @DatabaseName + '.' + @FullTableName + ''', 
            ''' + @CurrentColumn + ''', 
            LEFT(CONVERT(nvarchar(3630), ' + @CurrentColumn + '), 3630) 
          FROM ' + @FullTableName + '
          WHERE ' + @CurrentColumn + ' IS NOT NULL 
            AND CONVERT(nvarchar(3630), ' + @CurrentColumn + ') LIKE ' + @QueryString
        )
        SET NOCOUNT OFF;
      END TRY
      BEGIN CATCH
        PRINT '  Warning: Skipped searching ' + @DatabaseName + '.' + @FullTableName + 
              ' (Type: ' + @CurrentTableType + ') perhaps due to potential locking issues.';
        PRINT '    Message: ' + ERROR_MESSAGE();
        BREAK;
      END CATCH
    END
  END
  ELSE
  BEGIN
    PRINT 'Skipping ' + @DatabaseName + '.' + @FullTableName + ' [' + 
          CAST(@CurrentTableNumber AS varchar) + '/' + CAST(@TotalTables AS varchar) + 
          '] - object no longer exists';
  END

  -- 取得下一筆資料
  FETCH NEXT FROM table_cursor INTO @CurrentSchema, @CurrentTableName, @CurrentTableType;
END

-- 清理 Cursor
CLOSE table_cursor;
DEALLOCATE table_cursor;

-- 返回搜尋結果
SELECT TableType, FullTableName, ColumnName, ColumnValue FROM @SearchResults

2025-01-23 第三版

• 套上了 cursor 取代掉原本的 while
• SQL文排版了一下