一.本文所涉及的內容（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元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
