SQLServer 在資料庫中反向查詢字串值在哪個表裡

算是反向查詢吧，不知道表在那裏只知道數值，又正好這個數值足夠特別不是到處都有的，可以反向特定出位於哪個表格裡。

直接上代碼

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

-- 宣告用於循環處理的變數
DECLARE 
    @CurrentTable nvarchar(256),
    @CurrentColumn nvarchar(128),
    @QueryString nvarchar(110),
    @CurrentTableType nvarchar(10),
    @DatabaseName nvarchar(128)

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

-- 在資料庫中迭代所有的表格和視圖
WHILE @CurrentTable IS NOT NULL
BEGIN
    SET @CurrentColumn = ''

    -- 獲取下一個表格或視圖名稱
    SET @CurrentTable =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @CurrentTable
            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )

    -- 確定當前的表格/視圖類型
    SELECT @CurrentTableType = TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES
    WHERE QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) = @CurrentTable

    -- 打印當前搜尋的表格或視圖名稱
    PRINT 'Searching ' + @DatabaseName + '.' + @CurrentTable + ' (Type: ' + @CurrentTableType + ')';

    -- 迭代當前表格/視圖的所有列
    WHILE (@CurrentTable IS NOT NULL) AND (@CurrentColumn IS NOT NULL)
    BEGIN
        -- 獲取下一個列名稱
        SET @CurrentColumn =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@CurrentTable, 2)
                AND TABLE_NAME  = PARSENAME(@CurrentTable, 1)
                AND QUOTENAME(COLUMN_NAME) > @CurrentColumn
        )

        -- 如果該列存在，則搜尋目標字串
        IF @CurrentColumn IS NOT NULL
        BEGIN
            BEGIN TRY
                SET NOCOUNT ON;
                INSERT INTO @SearchResults
                EXEC
                (
                    'SELECT ''' + @CurrentTableType + ''', ' + 
                        '''' + @DatabaseName + '.' + @CurrentTable + ''', ' + 
                        '''' + @CurrentColumn + ''', ' + 
                        'LEFT(CONVERT(nvarchar(3630), ' + @CurrentColumn + '), 3630) ' + 
                    'FROM ' + @CurrentTable + ' (NOLOCK) ' + 
                    'WHERE ' + @CurrentColumn + ' IS NOT NULL' + 
                        ' AND CONVERT(nvarchar(3630), ' + @CurrentColumn + ') LIKE ' + @QueryString
                )
                SET NOCOUNT OFF;
            END TRY
            BEGIN CATCH
                PRINT '  Warning: Skipped searching ' + @DatabaseName + '.' + @CurrentTable + ' (Type: ' + @CurrentTableType + ') perhaps due to potential locking issues.';
                PRINT '    Message: ' + ERROR_MESSAGE();
                BREAK;
            END CATCH
        END
    END 
END

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

第二版本

1. 新增簡單的邏輯判斷(表格或VIEW無效不掃描)避開不必要警告
2. 新增簡單進度表 [目前/總共] 的輸出
3. 防鎖表由 NOLOCK 改成全域的交易等級 READ UNCOMMITTED

-- 宣告搜尋的字串和結果表格
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 
  @CurrentTable nvarchar(256),
  @CurrentColumn nvarchar(128),
  @QueryString nvarchar(110),
  @CurrentTableType nvarchar(10),
  @DatabaseName nvarchar(128),
  @TotalTables int,
  @CurrentTableNumber int

-- 初始設定
SET @CurrentTable = ''
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

-- 在資料庫中迭代所有的表格和視圖
WHILE @CurrentTable IS NOT NULL
BEGIN
  SET @CurrentColumn = ''

  -- 獲取下一個表格或視圖名稱
  SET @CurrentTable =
  (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
      AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @CurrentTable
      AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
  )

  SET @CurrentTableNumber = @CurrentTableNumber + 1

  -- 檢查表格或視圖是否存在
  IF @CurrentTable IS NOT NULL AND NOT EXISTS (
    SELECT 1 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(@CurrentTable)
      AND (type = 'U' OR type = 'V')
  )
  BEGIN
    PRINT 'Skipping ' + @DatabaseName + '.' + @CurrentTable + ' [' + CAST(@CurrentTableNumber AS varchar) + '/' + CAST(@TotalTables AS varchar) + '] - object no longer exists';
    CONTINUE
  END

  -- 確定當前的表格/視圖類型
  SELECT @CurrentTableType = TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
  WHERE QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) = @CurrentTable

  -- 打印當前搜尋的表格或視圖名稱
  PRINT 'Searching ' + @DatabaseName + '.' + @CurrentTable + ' [' + CAST(@CurrentTableNumber AS varchar) + '/' + CAST(@TotalTables AS varchar) + '] (Type: ' + @CurrentTableType + ')';

  -- 迭代當前表格/視圖的所有列
  WHILE (@CurrentTable IS NOT NULL) AND (@CurrentColumn IS NOT NULL)
  BEGIN
    -- 獲取下一個列名稱
    SET @CurrentColumn =
    (
      SELECT MIN(QUOTENAME(COLUMN_NAME))
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = PARSENAME(@CurrentTable, 2)
        AND TABLE_NAME  = PARSENAME(@CurrentTable, 1)
        AND QUOTENAME(COLUMN_NAME) > @CurrentColumn
    )

    -- 如果該列存在，則搜尋目標字串
    IF @CurrentColumn IS NOT NULL
    BEGIN
      BEGIN TRY
        SET NOCOUNT ON;
        INSERT INTO @SearchResults
        EXEC
        (
          'SELECT ''' + @CurrentTableType + ''', ' + 
            '''' + @DatabaseName + '.' + @CurrentTable + ''', ' + 
            '''' + @CurrentColumn + ''', ' + 
            'LEFT(CONVERT(nvarchar(3630), ' + @CurrentColumn + '), 3630) ' + 
          'FROM ' + @CurrentTable + ' ' + 
          'WHERE ' + @CurrentColumn + ' IS NOT NULL' + 
            ' AND CONVERT(nvarchar(3630), ' + @CurrentColumn + ') LIKE ' + @QueryString
        )
        SET NOCOUNT OFF;
      END TRY
      BEGIN CATCH
        PRINT '  Warning: Skipped searching ' + @DatabaseName + '.' + @CurrentTable + ' (Type: ' + @CurrentTableType + ') perhaps due to potential locking issues.';
        PRINT '    Message: ' + ERROR_MESSAGE();
        BREAK;
      END CATCH
    END
  END 
END

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