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
