SQL Server：在存在子字符串的多个数据库中查找所有列

Question

我正在使用一个SQL Server，该SQL Server在其模式中具有多个数据库（我们将其称为Alpha，Beta，Gamma等）。

我想浏览每个数据库中的每个数据库，并找到哪些列至少包含某个子字符串的值（例如，它是“狗”）。

I know that if I want to check if a certain column (COLUMN_NAME) contains this substring I could use

WHERE COLUMN_NAME LIKE '%dog%'

and if the table that is returned is not empty, that column had the sub string and if the table is empty, there was no substring "狗。”

For a more concrete example, let's say I have the table:

Person	Pet	Favorite Animal
Jack	Cat	Dog
James	Dog	Lion

I would want the result of my query to be (in some format) "Pet" and "Favorite Animal."

我不知道的是如何在所有列上迭代（检查每个列的任何值是否具有“狗”），更不用说所有数据库的所有列。

我该怎么做？这超出了SQL查询的范围（换句话说，这应该在Python之类的事情中完成）吗？

（为了清楚质疑而编辑。）

Answer 1

这是使用纯T-SQL进行操作的一种方法。它涉及光标，如果可能的话，最好避免使用动态的SQL WICH。首先循环浏览所有数据库，并获取类型Varchar/nvarchar的列（如果需要诸如text/ntext，可以添加其他类型）。然后使用动态SQL循环遍历所有列，以计算具有匹配的行。

警告：不要在较大的生产环境上运行此查询，因为它可能会对性能产生严重影响。

--Set the search query
DECLARE @search_query NVARCHAR(200) = '%dog%';

--Create temp output table
DROP TABLE IF EXISTS #columns;
CREATE TABLE #columns (
    _database_name NVARCHAR(4000),
    _schema_name NVARCHAR(4000),
    _table_name NVARCHAR(4000),
    _column_name NVARCHAR(4000),
    _count INT
);

/*
    1. Loop through all databases on server
    and get columns of type nvarchar och varchar
*/

DECLARE @database_name NVARCHAR(250);
DECLARE @sql NVARCHAR(MAX);

-- Create a cursor for database names except
DECLARE database_cursor CURSOR FOR
SELECT
    [name]
FROM sys.databases
WHERE
    --Modify this to exclude other databases
    name NOT IN ('SSISDB','master','tempdb','model','msdb');

OPEN database_cursor;

FETCH NEXT FROM database_cursor INTO @database_name;

-- Loop through the databases and insert columns in temp-table
WHILE @@FETCH_STATUS = 0
BEGIN 
    SET @sql = CONCAT(
        '
        INSERT INTO #columns (_database_name,_schema_name,_table_name,_column_name)
        SELECT ''',
            @database_name,''' database_name,
            s.name schema_name,
            t.name table_name,
            c.name column_name
        FROM [',@database_name,'].sys.all_objects t
        JOIN [',@database_name,'].sys.schemas s
        ON t.schema_id = s.schema_id
        JOIN [',@database_name,'].sys.all_columns c
        ON t.object_id = c.object_id
        JOIN [',@database_name,'].sys.types ty
        ON c.user_type_id = ty.user_type_id
        WHERE 
            ty.name IN (''nvarchar'',''varchar'') AND
            is_ms_shipped = 0 AND
            t.type = ''U''');

    EXEC (@sql);
    
    FETCH NEXT FROM database_cursor INTO @database_name;
END

CLOSE database_cursor;
DEALLOCATE database_cursor;

/* 
    Step 2: Loop through the columns of all 
    databases, count ocurrences of the query
    and add to temp table
*/
DECLARE @column_name NVARCHAR(4000),@table_name NVARCHAR(4000);
DECLARE column_cursor CURSOR FOR 
SELECT
CONCAT('[',_column_name,']'),
CONCAT('[',_database_name,'].[',_schema_name,'].[',_table_name,']')
FROM #columns

OPEN column_cursor;
FETCH NEXT FROM column_cursor INTO @column_name, @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN 

    SET @sql = CONCAT('UPDATE columns
    SET _count = row_search._count  
    FROM #columns columns
    JOIN (
    SELECT
    COUNT(*) _count
    FROM ',@table_name,'
    WHERE ',@column_name,' LIKE ''',@search_query,'''
    ) row_search ON 1=1
    WHERE 
        CONCAT(''['',_database_name,''].['',_schema_name,''].['',_table_name,'']'') = ''',@table_name,''' AND
        CONCAT(''['',_column_name,'']'') = ''',@column_name,''''
    )   
    ;
    EXEC (@sql);
FETCH NEXT FROM column_cursor INTO @column_name,@table_name;
END

CLOSE column_cursor;
DEALLOCATE column_cursor;

;
--Final output
SELECT 
    _database_name [Database],
    _schema_name [Schema],
    _table_name [Table],
    _column_name [Column],
    _count [Count]
FROM #columns