DECLARE @dbname VARCHAR(50) ,
@spname VARCHAR(50);
SET @spname = 'usp_his5_common_job';
DECLARE @t TABLE
(
Id INT IDENTITY ,
dbname VARCHAR(50) ,
spname VARCHAR(50)
);
DECLARE roy CURSOR
FOR
SELECT name
FROM master.dbo.sysdatabases;
OPEN roy;
FETCH NEXT FROM roy INTO @dbname;
WHILE @@fetch_status = 0
BEGIN
DECLARE @sql NVARCHAR(1000) ,
@i INT;
SET @sql = 'select @i=count(1) from' + ' ' + +@dbname
+ '.dbo.sysobjects where xtype=''P'' AND name=''' + @spname + '''';
EXEC sp_executesql @sql, N'@i int output', @i OUTPUT;
IF @i > 0
INSERT INTO @t
( dbname, spname )
SELECT @dbname ,
@spname;
FETCH NEXT FROM roy INTO @dbname;
END;
CLOSE roy;
DEALLOCATE roy;
SELECT *
FROM @t;
根据存储过程名字查询存储过程所在数据库
最新推荐文章于 2023-05-08 10:35:47 发布