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