SQL Server 在多个数据库中创建同一个存储过程（Create Same Stored Procedure in All Databases）

时间：2022-03-16 09:52

一.本文所涉及的内容（Contents）

本文所涉及的内容（Contents）
背景（Contexts）
遇到的问题（Problems）
实现代码（SQL Codes）
1. 方法一：拼接SQL；
2. 方法二：调用模板存储过程创建存储过程；
3. 总结
4. 扩展阅读
参考文献（References）

二.背景（Contexts）

　　在我的数据库服务器上，同一个实例下面挂载着许多相同结构的数据库，他们为不同公司提供着服务，在许多时候我需要同时创建、修改、删除一些对象，存储过程就是其中一个，但是想要批量创建存储，这有些特殊，下面就教你如何实现在多个数据库中创建同一个存储过程（Create Same Stored Procedure in All Databases）。

三.遇到的问题（Problems）

　　在之前的文章中多次谈到使用游标的方式处理的各种问题：

-- Script1：
-- 需要被批量创建的存储过程
USE [master]
GO
Create PROCEDURE [dbo].[sp_GetId]
AS
BEGIN
    DECLARE @database_id INT
    SET @database_id = 0
    SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]

END

　　根据前面提到使用游标方式，我们可能会写出类似下面的代码，错误代码Script2示例：

-- Script2：
-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2014/05/03>
-- Description: <批量创建存储过程，错误代码示例>
-- =============================================
DECLARE @databaseName VARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @itemCur CURSOR

SET @itemCur = CURSOR FOR
    SELECT ‘[‘+[name]+‘]‘ FROM sys.databases WHERE database_id > 4

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @databaseName
WHILE @@FETCH_STATUS=0
BEGIN
    --逻辑处理
    PRINT @databaseName
    
    SET @SQL = ‘
    USE ‘+@databaseName+‘
    GO
    CREATE PROCEDURE [dbo].[sp_GetId]
AS
BEGIN
    DECLARE @database_id INT
    SET @database_id = 0
    SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]

END‘
    PRINT(@SQL);
    EXEC(@SQL);
    
    FETCH NEXT FROM @itemCur INTO @databaseName
END 

CLOSE @itemCur
DEALLOCATE @itemCur

执行上面的代码你会遇到这样的错误信息：

gxlsystem.com,布布扣

（Figure5：创建了sp_GetId存储过程的数据库列表）

(三) 总结

　　上面已经通过两种方式实现了在多个数据库中创建同一个存储过程，如果存储过程sp_GetId属于比较简单的，使用方式1实现会比较快捷，如果sp_GetId比较复杂了，比如存储过程里面还包含单引号或者代码比较多的情况下，建议使用方式2，虽然方式2的步骤会多一点，但是只要创建好模板存储过程，其它的根本不会因为存储过程sp_GetId而变得复杂；

(四) 扩展阅读

　　在实际运用中，很多时候你需要的并不单单是在多个数据库中创建同一个存储过程，可能还需要修改同一个存储过程，通过上面的阅读你也许猜到修改存储过程，可以先删除，再创建，对的，这是没有问题的，不过也可以直接修改，下面提供SQL代码：

　　1) 首先修改下master数据库的模板存储过程sp_GetId，在存储过程里面中加入一个变量@id：

-- Script10：
-- 需要被批量创建的存储过程
USE [master]
GO
Create PROCEDURE [dbo].[sp_GetId]
AS
BEGIN
    DECLARE @database_id INT
    --修改部分,增加了一个变量
    DECLARE @id INT
    SET @database_id = 0
    SELECT TOP 1 @database_id = [database_id] FROM sys.[databases]

END

　　2) 接着创建一个修改存储过程的存储过程AlterProcedure，只需要把变量@proc_text里面的“CREATE PROC”替换成“ALTER PROC”就可以了：

-- Script10：
--修改存储过程的存储过程
USE [master]
GO
-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2014.05.06>
-- Description: <修改存储过程的存储过程>
-- =============================================
CREATE PROC AlterProcedure
(
    @dbname SYSNAME,
    @spname SYSNAME
)
AS
BEGIN
    SELECT @dbname = REPLACE(REPLACE(@dbname,‘[‘,‘‘),‘]‘,‘‘)

    IF @dbname <> ‘master‘
    BEGIN
        DECLARE @proc_text NVARCHAR(MAX)
        SELECT @proc_text = REPLACE([text],‘‘‘‘,‘‘‘‘‘‘)
            FROM [sysobjects] o
            INNER JOIN [syscomments] c
            ON c.id = o.id
        WHERE
            o.type = ‘P‘ AND
            o.name = @spname

        DECLARE @sql NVARCHAR(MAX)
        SET @proc_text = REPLACE(@proc_text,‘CREATE PROC‘,‘ALTER PROC‘)
        SET @sql = ‘USE [‘ + @dbname + ‘]; EXEC (‘‘ ‘ + @proc_text + ‘‘‘);‘

        EXEC SP_EXECUTESQL @sql
    END
END
GO

　　3) 准备完上面的步骤，再把Script8的脚本中调用存储过程CreateProcedure改成调用存储过程AlterProcedure，通过下面的一条SQL语句批量修改存储过程sp_GetId：

-- Script11：
-- 批量修改存储过程
USE [master]
GO

--过滤数据库
EXEC [sp_MSforeachdb_Filter] @command1=‘AlterProcedure ‘‘[?]‘‘, ‘‘sp_GetId‘‘‘,
@whereand=" and [name] not in(‘tempdb‘,‘master‘,‘model‘,‘msdb‘) "

　　4) 创建完成后，剩下的就是验证下数据库中存储过程sp_GetId的内容了；