求编写SQL Server跨多数据库合并两类日志表的循环执行函数

SQL Server的标量/表值函数无法直接执行动态SQL，因此跨所有数据库实现联合查询，更实用的方式是使用存储过程，或者动态生成可复用的视图。以下是具体实现方案：

方案一：用存储过程实现跨库联合查询 #

合并Refresh Log表的存储过程 #

CREATE PROCEDURE GetCombinedRefreshLog
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DBName NVARCHAR(128);
    DECLARE @SQL NVARCHAR(MAX) = N'';

    -- 遍历所有在线的用户数据库（排除系统库）
    DECLARE db_cursor CURSOR FOR
        SELECT name 
        FROM sys.databases 
        WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
          AND state_desc = 'ONLINE';

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @DBName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接每个数据库的查询语句，表名含空格需用方括号包裹
        IF @SQL <> N''
            SET @SQL = @SQL + N'UNION ALL ';

        SET @SQL = @SQL + N'
            SELECT 
                ''' + @DBName + ''' AS SourceDatabase, -- 标记数据来源库
                * 
            FROM [' + @DBName + '].[dbo].[Refresh Log]'; -- 若表不在dbo架构下，需修改此处

        FETCH NEXT FROM db_cursor INTO @DBName;
    END;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    -- 执行动态SQL
    EXEC sp_executesql @SQL;
END;

合并Detailed refresh log表的存储过程 #

CREATE PROCEDURE GetCombinedDetailedRefreshLog
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DBName NVARCHAR(128);
    DECLARE @SQL NVARCHAR(MAX) = N'';

    DECLARE db_cursor CURSOR FOR
        SELECT name 
        FROM sys.databases 
        WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
          AND state_desc = 'ONLINE';

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @DBName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @SQL <> N''
            SET @SQL = @SQL + N'UNION ALL ';

        SET @SQL = @SQL + N'
            SELECT 
                ''' + @DBName + ''' AS SourceDatabase,
                * 
            FROM [' + @DBName + '].[dbo].[Detailed refresh log]';

        FETCH NEXT FROM db_cursor INTO @DBName;
    END;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    EXEC sp_executesql @SQL;
END;

使用方法

直接执行存储过程即可获取合并结果：

-- 获取合并后的Refresh Log
EXEC GetCombinedRefreshLog;

-- 获取合并后的Detailed refresh log
EXEC GetCombinedDetailedRefreshLog;

方案二：动态生成可复用视图 #

如果需要像查询普通表一样调用合并结果，可以动态生成视图，每次执行脚本更新视图内容：

生成合并Refresh Log的视图 #

DECLARE @SQL NVARCHAR(MAX) = N'CREATE OR ALTER VIEW vw_CombinedRefreshLog AS ';

DECLARE @DBName NVARCHAR(128);
DECLARE db_cursor CURSOR FOR
    SELECT name 
    FROM sys.databases 
    WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
      AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @SQL <> N'CREATE OR ALTER VIEW vw_CombinedRefreshLog AS '
        SET @SQL += N'UNION ALL ';

    SET @SQL += N'
        SELECT 
            ''' + @DBName + ''' AS SourceDatabase,
            * 
        FROM [' + @DBName + '].[dbo].[Refresh Log]';

    FETCH NEXT FROM db_cursor INTO @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

EXEC sp_executesql @SQL;

生成后可直接查询：

SELECT * FROM vw_CombinedRefreshLog;

生成合并Detailed refresh log的视图 #

DECLARE @SQL NVARCHAR(MAX) = N'CREATE OR ALTER VIEW vw_CombinedDetailedRefreshLog AS ';

DECLARE @DBName NVARCHAR(128);
DECLARE db_cursor CURSOR FOR
    SELECT name 
    FROM sys.databases 
    WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
      AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @SQL <> N'CREATE OR ALTER VIEW vw_CombinedDetailedRefreshLog AS '
        SET @SQL += N'UNION ALL ';

    SET @SQL += N'
        SELECT 
            ''' + @DBName + ''' AS SourceDatabase,
            * 
        FROM [' + @DBName + '].[dbo].[Detailed refresh log]';

    FETCH NEXT FROM db_cursor INTO @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

EXEC sp_executesql @SQL;

关键注意事项 #

• 确保所有数据库中目标表的结构完全一致，否则UNION ALL会报错；若结构有差异，需指定具体列名而非使用*。
• 脚本默认表在dbo架构下，若实际架构不同，需修改对应位置的架构名称。
• 可根据需求调整WHERE条件中的数据库排除列表，移除不需要的数据库。
• 执行脚本的账号需拥有所有目标数据库的查询权限。

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