亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL Server 在多個數據庫中創建同一個存儲過程

系統 2465 0
原文: 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)

  在之前的文章中多次談到使用游標的方式處理的各種問題:

  如果使用游標來批量創建存儲過程,可能你會遇到下面的一些問題,假設我們需要在多個數據庫(當然可以過濾掉部分數據庫)中創建同樣一個存儲過程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

SQL Server 在多個數據庫中創建同一個存儲過程(Create Same Stored Procedure in All Databases)


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 免费福利入口在线观看 | 亚洲毛片在线免费观看 | 91在线成人| 久久精品伊人波多野结 | 四虎影院视频在线观看 | 精品国产三级 | 国产麻豆精品手机在线观看 | 亚洲春色综合另类网蜜桃 | 福利视频精品 | 久久国产国内精品对话对白 | 青青青国产免费线在 | 58av国产精品 | 视频国产91 | 亚洲91av| 国产91久久久久久久免费 | 天天操天天噜 | 99re这里只有精品在线 | 色天天干 | 性色网站 | 狠狠狠地在啪线香蕉 | 日韩欧美亚洲每日更新网 | 看特级大黄一片 | 色噜噜狠狠狠狠色综合久一 | 欧美亚洲高清日韩成人 | 国产在线观看美女福利精 | 亚洲高清免费视频 | 欧美日韩大尺码免费专区 | 天天操天天碰 | 91成人免费观看在线观看 | 日韩久久久精品首页 | 看日本真人一一级特黄毛片 | 国语高清精品一区二区三区 | 久久国产午夜精品理论片34页 | 亚洲啊v| 国产99免费视频 | 狠狠色视频 | 久热这里| 午夜影院普通 | 波多野结衣免费免费视频一区 | 日本久久伊人 | 亚洲国产精品毛片∧v卡在线 |