SQL Server: 快速判断某个库是否存在某个对象 (表/函数/存储过程等均可)
--遍历所有数据库，快速判断某个库是否存在某个对象 (表/函数/存储过程等均可)SET NOCOUNT ONDECLARE @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 --设定循环变量--循环所有DBWHILE @i<=@iMaxBEGINSELECT @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 outputUPDATE @dbList SET isExists = 1 WHERE rowNum=@i AND @isExists > 0SET @i=@i+1;END--查看结果SELECT * FROM @dbList