Sql Server数据库存储内容查询

背景：需要查找到数据库中某些内容的存储数据表，对数据库进行逐一排查

DECLARE @SearchString NVARCHAR(255)
SET @SearchString = '高新' -- 你要搜索的字符串

DECLARE @TableSchema NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

-- 创建一个临时表来存储结果
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName NVARCHAR(128), ColumnName NVARCHAR(128), ColumnValue NVARCHAR(MAX))

-- 游标来遍历所有表和列
DECLARE cur CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
-- 确保指定的数据库名称
-- AND TABLE_CATALOG = 'YourDatabaseName' -- 替换为你的数据库名称，如果需要限定数据库

OPEN cur
FETCH NEXT FROM cur INTO @TableSchema, @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'INSERT INTO #Results (TableName, ColumnName, ColumnValue)
                SELECT ''' + @TableSchema + '.' + @TableName + ''' AS TableName, ''' + @ColumnName + ''' AS ColumnName, [' + @ColumnName + '] AS ColumnValue
                FROM [' + @TableSchema + '].[' + @TableName + ']
                WHERE [' + @ColumnName + '] LIKE ''%' + @SearchString + '%'''

    EXEC sp_executesql @SQL
    FETCH NEXT FROM cur INTO @TableSchema, @TableName, @ColumnName
END

CLOSE cur
DEALLOCATE cur

-- 查询结果
SELECT * FROM #Results

-- 清理临时表
DROP TABLE #Results