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
第二版本
- 新增簡單的邏輯判斷(表格或VIEW無效不掃描)避開不必要警告
- 新增簡單進度表 [目前/總共] 的輸出
- 防鎖表由 NOLOCK 改成全域的交易等級 READ UNCOMMITTED
DECLARE @TargetString nvarchar(255) = '<要檢索的字串>';
DECLARE @SearchResults TABLE (
TableType nvarchar(10),
FullTableName nvarchar(500),
ColumnName nvarchar(370),
ColumnValue nvarchar(3630)
);
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
