代码改变世界

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

2014-05-13 10:35 by 听风吹雨, ... 阅读, ... 评论, 收藏, 编辑

一.本文所涉及的内容(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)

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

  如果使用游标来批量创建存储过程,可能你会遇到下面的一些问题,假设我们需要在多个数据库(当然可以过滤掉部分数据库)中创建同样一个存储过程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

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

wps_clip_image-14323

(Figure1:错误信息1)

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

wps_clip_image-8651

(Figure2:错误信息2)

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

wps_clip_image-17048

(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所示:

wps_clip_image-6113

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

wps_clip_image-14563

(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