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