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