如何基于带通配符的配置表查询跨库匹配的数据表列表

原有代码问题 #

• 硬编码了BIML数据库的系统视图路径，仅能读取该库的元数据，无法适配配置表中TEST等其他数据库
• 两次关联datalist表的逻辑错误，schema和表名需要匹配同一条配置规则，分开关联会导致规则匹配错乱

实现代码 #

因为要跨多个数据库查询系统元数据，需要使用动态SQL遍历所有配置的数据库，代码如下：

-- 定义临时表存储最终结果
CREATE TABLE #result (
    TableName sysname,
    selectquery NVARCHAR(MAX),
    schemaname sysname,
    [order] INT
)

DECLARE @dbname sysname, @sql NVARCHAR(MAX)

-- 遍历所有配置中出现的不同数据库
DECLARE db_cursor CURSOR FOR
SELECT DISTINCT [dbname] FROM [UITWISSEL].[config].[datalist] WHERE [dbname] IS NOT NULL

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成当前库的匹配查询SQL
    SET @sql = N'
    INSERT INTO #result (TableName, selectquery, schemaname, [order])
    SELECT DISTINCT
        t.name AS TableName,
        ''SELECT * FROM '' + QUOTENAME(''' + @dbname + N''') + ''.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) AS selectquery,
        s.name AS schemaname,
        ds.[order]
    FROM ' + QUOTENAME(@dbname) + N'.sys.schemas s
    INNER JOIN ' + QUOTENAME(@dbname) + N'.sys.tables t ON s.schema_id = t.schema_id
    INNER JOIN [UITWISSEL].[config].[datalist] ds
        ON ds.[dbname] = ''' + @dbname + N'''
        AND s.name LIKE REPLACE(ds.schemaname, ''*'', ''%'') COLLATE DATABASE_DEFAULT
        AND t.name LIKE REPLACE(ds.tablename, ''*'', ''%'') COLLATE DATABASE_DEFAULT
    '
    EXEC sp_executesql @sql
    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- 输出最终结果
SELECT * FROM #result ORDER BY [order], schemaname, TableName

DROP TABLE #result

逻辑说明 #

1. 先创建临时表存储所有数据库的匹配结果
2. 用游标遍历配置表中所有去重的数据库名称，避免重复查询
3. 对每个数据库动态生成元数据查询语句，关联配置表匹配当前库下符合规则的schema和表
4. 所有库查询完成后统一输出排序后的结果，完全匹配给定的4条配置规则

注意：执行该代码的账号需要拥有所有配置数据库的系统视图查询权限，否则会出现权限不足报错。

内容的提问来源于stack exchange，提问作者Henrov