一.本文所涉及的內容(Contents)
二.背景(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
SQL Server 在多個數據庫中創建同一個存儲過程(Create Same Stored Procedure in All Databases)
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

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