SQL Server 在多个数据库中创建同一个存储过程

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

-- Script1：-- 需要被批量创建的存储过程USE [master]GOCreate PROCEDURE [dbo].[sp_GetId]ASBEGIN    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 CURSORSET @itemCur = CURSOR FOR    SELECT '['+[name]+']' FROM sys.databases WHERE database_id > 4OPEN @itemCurFETCH NEXT FROM @itemCur INTO @databaseNameWHILE @@FETCH_STATUS=0BEGIN    --逻辑处理    PRINT @databaseName        SET @SQL = '    USE '+@databaseName+'    GO    CREATE PROCEDURE [dbo].[sp_GetId]ASBEGIN    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 @databaseNameEND CLOSE @itemCurDEALLOCATE @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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOcreate 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) = nullas    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]GODECLARE @SQL NVARCHAR(MAX)SELECT @SQL = COALESCE(@SQL,'') + ' USE [?]; EXEC(''CREATE PROCEDURE [dbo].[sp_GetId]ASBEGIN    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'))BEGINCREATE TABLE [master].[dbo].[SPList](    [db_name] [sysname] NULL,    [sp_name] [sysname] NULL,) ON [PRIMARY]ENDELSE    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]GODECLARE @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)ASBEGIN    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    ENDENDGO

　　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]GOCreate PROCEDURE [dbo].[sp_GetId]ASBEGIN    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)ASBEGIN    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    ENDENDGO

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