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

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

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

二.背景（Contexts）

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

三.遇到的问题（Problems）

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

• SQL Server 游标运用：查看所有数据库所有表大小信息（Sizes of All Tables in All Database）
• SQL Server 游标运用：查看一个数据库所有表大小信息（Sizes of All Tables in a Database）

　　如果使用游标来批量创建存储过程，可能你会遇到下面的一些问题，假设我们需要在多个数据库（当然可以过滤掉部分数据库）中创建同样一个存储过程sp_GetId，存储过程的脚本如下Script1所示：

-- 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

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

（Figure1：错误信息1）

根据错误信息修改上面的SQL代码，把”GO”改成“;”但还是会出现下图Figure2的错误信息：

（Figure2：错误信息2）

既然这样行不通，也许你还会尝试在[dbo].[sp_GetId]前面加上数据库名的变量，但是却出现下图Figure3的错误信息：

（Figure3：错误信息3）

四.实现代码（SQL Codes）

　　上面的3个错误让我们陷入了困境，也许你想过放弃了，但是经过努力，我通过2种方式实现了在多个数据库中创建同一个存储过程（大家可认为是批量创建存储过程），下面是实现的2种方式概述：

　　1. 通过修改过的系统存储过程[dbo].[sp_MSforeachdb_Filter]和拼接SQL；

　　2. 通过创建一个模板存储过程，由系统存储过程sp_MSForEachDB循环调用另外一个创建存储过程的存储来创建模板存储过程（这也许听起来很拗口，看后面的实现脚本Script7，你就会理解了）。

(一) 实现方式1：通过修改过的系统存储过程[dbo].[sp_MSforeachdb_Filter]和拼接SQL；

　　1) 首先我们需要在master数据库中创建一个存储过程[dbo].[sp_MSforeachdb_Filter]，这是通过修改系统存储过程sp_MSforeachdb得来的，做的改进主要是可以过滤数据库，创建的SQL代码如下Script3所示：

-- Script3：
-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2013.05.06>
-- Description: <扩展sp_MSforeachdb，增加@whereand参数>
-- =============================================
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

create proc [dbo].[sp_MSforeachdb_Filter]
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
    @whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    set deadlock_priority low

    /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
    select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */

    if (@precommand is not null)
        exec(@precommand)

    declare @origdb nvarchar(128)
    select @origdb = db_name()

    /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* Create the select */
    exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
            N' where (d.status & ' + @inaccessible + N' = 0)' +
            N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)

    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

   declare @tempdb nvarchar(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')

    return @retval

　　2) 接着在master数据库中执行下面的SQL在多个数据库中创建同一个存储过程，其实是把需要创建的存储过程通过拼接保存在@SQL变量中，使用[sp_MSforeachdb_Filter]来过滤数据库，并在符合条件的每个数据库中执行@SQL中的语句，SQL代码如下Script4所示：

-- Script4：
--批量创建存储过程
USE [master]
GO
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''
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 [sp_MSforeachdb_Filter] @command1=@SQL,
@whereand=" and [name] not in('tempdb','master','model','msdb') "

　　3) 执行上面的SQL脚本之后，除了('tempdb','master','model','msdb')4个数据库之外的数据库都会创建了存储过程sp_GetId，为了快速验证，可以使用下面的SQL脚本进行验证：

-- Script5：
-- 返回所有数据库sp_GetId存储过程列表
IF NOT EXISTS (SELECT * FROM [master].sys.objects
    WHERE object_id = OBJECT_ID(N'[master].[dbo].[SPList]') AND type in (N'U'))
BEGIN
CREATE TABLE [master].[dbo].[SPList](
    [db_name] [sysname] NULL,
    [sp_name] [sysname] NULL,
) ON [PRIMARY]
END
ELSE
    TRUNCATE TABLE [master].[dbo].[SPList]

EXEC [sp_MSforeachdb_Filter] @command1='
    INSERT INTO [master].[dbo].[SPList]([sp_name])
        SELECT [name] FROM [?].sys.[sysobjects] WHERE TYPE = ''P'' AND [name] = ''sp_GetId''
    UPDATE [master].[dbo].[SPList] SET [db_name] = ''?'' WHERE [db_name] IS NULL
'

SELECT * FROM [master].[dbo].[SPList]

执行上面的SQL脚本的结果如下图Figure4所示：

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

(二) 实现方式2：通过创建一个模板存储过程，由系统存储过程sp_MSForEachDB循环调用另外一个创建存储过程的存储来创建模板存储过程；

　　1) 为了能看到方式2的实际效果，我们需要把存在sp_GetId存储过程的数据库中批量删除这个存储过程，通过下面的脚本Script6来实现：

-- Script6：
-- 批量删除存储过程
USE [master]
GO
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''
IF  EXISTS (SELECT * FROM sys.[objects]
    WHERE object_id = OBJECT_ID(N''''[dbo].[sp_GetId]'''')
    AND type in (N''''P'''', N''''PC''''))
DROP PROCEDURE [dbo].[sp_GetId] '')'

PRINT @SQL

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

　　2) 通过Script5确认所有数据库都不存在sp_GetId存储过程；

　　3) 接着在master数据库中创建模板存储过程sp_GetId，创建脚本如Script1所示，这里不做重复；

　　4) 再接着创建一个存储过程CreateProcedure，这个存储过程的作用就是创建存储过程，在这个存储过程CreateProcedure利用系统表返回sp_GetId存储过程的内容，保存在变量@proc_text中，查询出如下所示：

-- Script7：
--创建存储过程的存储过程
USE [master]
GO
-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2014.05.06>
-- Description: <创建存储过程的存储过程>
-- =============================================
CREATE PROC CreateProcedure
(
    @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 @sql = 'USE [' + @dbname + ']; EXEC ('' ' + @proc_text + ''');'

        EXEC SP_EXECUTESQL @sql
    END
END
GO

　　5) 准备完上面的步骤，只需要下面的一条SQL语句就能批量创建存储过程sp_GetId：

-- Script8：
-- 批量创建存储过程
USE [master]
GO

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

执行上面的SQL脚本的结果如下图Figure5所示，与Figure4的区别就是在master数据库中多了一个模板存储过程sp_GetId。

（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的内容了；

五.参考文献（References）

Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example