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.
No comments:
Post a Comment