use master
godeclare @stmt varchar(4000);
declare @dbname sysname, @cnt int;-- 创建存储过程
declare @proc varchar(4000);
set @proc = 'create procedure dbo.usp_getproducts @price money as '+
'select * from products where UnitPrice>@price;';-- dbid > 4 的为用户数据库
declare c cursor local for
select name from sysdatabases where dbid>4;
open c;fetch next from c into @dbname;
while @@FETCH_STATUS = 0
begin
set @stmt = 'use ['+@dbname+']'+CHAR(13)+
-- 验证 products 表是否存在
'if object_id(''[dbo].[products]'') is not null'+CHAR(13)+
'begin'+CHAR(13)+
'exec('''+@proc+''');'+CHAR(13)+
'end';
exec(@stmt);
fetch next from c into @dbname;
endclose c;
EXEC sp_MSforeachdb
一句搞定