Wednesday, November 3, 2010

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.

1 comment:

7x3w4rul13 said...

The making use of wagering necessities for the deposit plus bonus amount are 35x. The whole package is value 5 BTC and it must be wagered 40x before cashing out. With Cafe Casino, all new gamers who deposit via Bitcoin, will get to assert a 350% welcome bonus of a lot as} $2,500. It’s solely valid in your first deposit, so you higher make it rely. If you deposit via crypto might get} a good 파라오카지노 higher bonus.