SQL Server - Search SQL object used in all database

Some times we pass through situation where we need to search specific object ever used in all databases.


Example: Finding whether Employee table in Payroll database is used in Accounts database. If yes than which are views, procedures table has been  used.


Below is simple query which will browse through each database to search given Table




DECLARE @DBNAME NVARCHAR(100)
DECLARE dbCUR CURSOR FOR SELECT name FROM SYS.DATABASES
open dbcur


FETCH DBCUR INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

--find in views

DECLARE @sql nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @cnt int

SET @sql = N'SELECT @cntout = count(*) FROM [' + @dbname + '].INFORMATION_SCHEMA.VIEWS WHERE view_definition like ''%Employee%'''
SET @ParmDefinition = N'@cntout int OUTPUT';

EXECUTE sp_executesql @sql,@ParmDefinition,@cntout=@cnt output



if @cnt > 0 
begin
print convert(nvarchar(10),@cnt) + ' views in ' + @dbname
end

--find procedure

SET @sql = N'SELECT @cntout = count(*) FROM [' + @dbname + '].sys.sql_modules modu
INNER JOIN [' + @dbname + '].sys.objects obj ON modu.object_id = obj.object_id
WHERE obj.type = ''P'' AND modu.definition like ''%%Employee%'''


SET @ParmDefinition = N'@cntout int OUTPUT';
EXECUTE sp_executesql @sql,@ParmDefinition,@cntout=@cnt output



if @cnt > 0 
begin
print convert(nvarchar(10),@cnt) + ' procedures in ' + @dbname
end

FETCH DBCUR INTO @DBNAME
END


close dbcur
deallocate dbcur

Hope it helps.