SQL Server: 高速判断某个库是否存在某个对象 (表/函数/存储过程等均可)

SQL Server: 快速判断某个库是否存在某个对象 (表/函数/存储过程等均可)

--遍历所有数据库，快速判断某个库是否存在某个对象 (表/函数/存储过程等均可)
SET NOCOUNT ON
DECLARE @obj_Name VARCHAR(MAX),@i INT,@iMax INT,@dbName VARCHAR(MAX),@sql NVARCHAR(MAX),@isExists INT;
DECLARE @dbList TABLE (rowNum INT, dbName VARCHAR(MAX), isExists BIT DEFAULT(0));
SET @obj_Name = 'Fun_Mobile_Type'		--设置查找对象
INSERT INTO @dbList (rowNum, dbName) SELECT ROW_NUMBER() OVER (ORDER BY NAME), name FROM sys.databases d WHERE d.name NOT IN ('master','model','msdb','tempdb')
SELECT @i=1,@iMax=COUNT(1) FROM @dbList --设定循环变量
--循环所有DB
WHILE @i<=@iMax
BEGIN
	SELECT @dbName=dbName FROM @dbList dl WHERE dl.rowNum=@i;
	SET @sql='SELECT @isExists=count(1) FROM '+@dbName+'.sys.objects WHERE object_id = OBJECT_ID('''+@dbName+'.[dbo].'+@obj_Name+''')';
	exec sp_executesql @sql, N'@isExists int output', @isExists output
	UPDATE @dbList SET isExists = 1 WHERE rowNum=@i AND @isExists > 0
	SET @i=@i+1;
END
--查看结果
SELECT * FROM @dbList

主要是用于服务器上多个同类db的查找...