22,210
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(max)
select @sql=isnull(@sql+' union all ','')+'select name from '+name+'..sysobjects where type=''p'' and name=''P__AA_BB_CC ''' from sys.databases
where name like'%db%'
exec( @sql)
DECLARE @name sysname;
SET @name = N'cs'
DECLARE @cmd NVARCHAR(4000);
SET @cmd = N'
INSERT ##tb(dbname,procedureName)
SELECT ''?'',name
FROM ?..sysobjects
WHERE xtype=''P'' AND name LIKE N'''+@name+'''
';
CREATE TABLE ##tb(dbname sysname,procedureName sysname);
EXEC sp_MsForeachdb @cmd;
SELECT * FROM ##tb;
DROP TABLE ##tb;
CREATE TABLE ##tb(dbname sysname,procedureName sysname);
exec sp_MsForeachdb N'
INSERT ##tb(dbname,procedureName)
SELECT ''?'',name
FROM ?..sysobjects
WHERE xtype=''p'' and name = ''P__AA_BB_CC''
';
SELECT * FROM ##tb;
DROP TABLE ##tb;
CREATE TABLE ##tb(dbname sysname,procedureName sysname);
exec sp_MsForeachdb N'
INSERT ##tb(dbname,procedureName)
SELECT ''?'',name
FROM ?.sys.procedures
WHERE name LIKE ''cs''
';
SELECT * FROM ##tb;
DROP TABLE ##tb;
SELECT NAME FROM DB_1..SYSOBJECTS WHERE TYPE='P' AND NAME=''
UNION ALL
SELECT NAME FROM DB_2..SYSOBJECTS WHERE TYPE='P' AND NAME=''
UNION ALL
SELECT NAME FROM DB_3..SYSOBJECTS WHERE TYPE='P' AND NAME=''
UNION ALL
SELECT NAME FROM DB_4..SYSOBJECTS WHERE TYPE='P' AND NAME=''
UNION ALL
SELECT NAME FROM DB_5..SYSOBJECTS WHERE TYPE='P' AND NAME=''
--如果庫比較多,動態遍历
sp_MSforeachtable --sp_MSforeachdb用法相同
@command1 nvarchar(2000), --第一条运行的T-SQL
@replacechar nchar(1) = N'?', --指定的占位符
@command2 nvarchar(2000) = null, --第二条运行的T-SQL
@command3 nvarchar(2000) = null, --第三条运行的T-SQL
@whereand nvarchar(2000) = null, --表的选择条件
@precommand nvarchar(2000) = null, --在表前执行的指令
@postcommand nvarchar(2000) = null --在表后执行的指令