无需sp_MSforeachdb与系统存储过程，如何跨库执行存储过程？

核心思路 #

通过动态SQL遍历系统数据库列表，切换到目标数据库上下文后执行数据合并逻辑，无需将存储过程部署到每个数据库，也不用依赖sp_MSforeachdb或系统存储过程，数据库增减时自动适配。

具体实现代码 #

以下是修正后的存储过程，直接在中心库（如central）部署即可：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_updateee] 
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量存储数据库名和动态SQL语句
    DECLARE @DBName NVARCHAR(128), @DynamicSQL NVARCHAR(MAX);

    -- 游标遍历所有在线的用户数据库（排除系统库和中心库）
    DECLARE DB_Cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'central')
      AND state_desc = 'ONLINE'; -- 仅处理在线数据库

    OPEN DB_Cursor;
    FETCH NEXT FROM DB_Cursor INTO @DBName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 构造动态SQL：切换到目标数据库，执行MERGE逻辑
        SET @DynamicSQL = N'
        USE [' + @DBName + N'];
        MERGE INTO central.dbo.consol WITH (holdlock) L
        USING (
            SELECT 
                ''fa'' AS falcon,
                ''be'' AS beaver,
                u.raddate,
                u.sessionid, 
                u.rust
            FROM tablea u
            WHERE u.raddate >= DATEADD(day, -7, GETDATE())
        ) ca
        ON L.sessionid = ca.sessionid AND L.rust = ca.rust
        WHEN NOT MATCHED THEN
            INSERT (falcon, beaver, raddate, sessionid, rust)
            VALUES (ca.falcon, ca.beaver, ca.raddate, ca.sessionid, ca.rust);
        ';

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

        FETCH NEXT FROM DB_Cursor INTO @DBName;
    END

    CLOSE DB_Cursor;
    DEALLOCATE DB_Cursor;
END
GO

方案优势 #

• 无需逐个部署存储过程：仅需在中心库维护这一个存储过程即可
• 自动适配数据库变化：新增/删除数据库时，只要数据库存在tablea且在线，就会被自动纳入处理范围
• 避开局限性：不依赖sp_MSforeachdb（该存储过程无官方文档，存在潜在bug），也无需将存储过程设为系统存储过程

关键细节说明 #

1. 数据库过滤：通过sys.databases筛选数据库，排除系统库和中心库，避免无效执行
2. 上下文切换：用USE [数据库名]切换到目标库，确保查询的是对应库的tablea表
3. 动态SQL安全：使用sp_executesql执行动态SQL，比直接EXEC更安全，后续若需参数化也更易扩展
4. 原逻辑修正：将原示例中VALUES('fa','be')作为默认值直接嵌入SELECT，简化了冗余的CROSS APPLY写法（若需动态默认值，可改为参数传入）

原示例代码的中文翻译 #

原示例代码（仅作演示，可能存在语法问题）：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_updateee] 
AS

MERGE INTO dw.All_Socks WITH (holdlock) L
USING (
SELECT ca.*
FROM   (VALUES('fa', 
               'be')) DefaultValues([falcon], [beaver])
       CROSS APPLY (

                   SELECT 
                          falcon,
                          beaver,
                          raddate,
                          sessionid, 
                          rust
                    FROM   "Instance" u
                    WHERE  
                        raddate >= DATEADD(day,-7, GETDATE())
                        ) ca)  ca(falcon, beaver, raddate, sessionid, rust),
                    ON L.sessionid = ca.sessionid AND L.rust = ca.rust
                    WHEN NOT matched THEN
                      INSERT
                      VALUES (
                      falcon,
                      beaver,
                      raddate,
                      sessionid,
                      rust

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