CODEWITHSUNDEEP
sql-serverreferencedependenciessp-msforeachdb
jayesh kshirsagar
jayesh kshirsagar

Reputation: 23

Reference of table in all databases of the Server

I want to find the reference of particular table in all the databases of the particular server. Example : My query window is opened for DB1 database and executed the query as:
exec sp_depends 'dbo.table1'
It return me all the references of the table table1 in the database objects like (procedures,functions, triggers) of database DB1 only but I want to know the references of the table in all other database on the same server without going to particular database query window everytime and running the query.

exec sp_depends 'dbo.table1'
I tried below statement for all databases but it did not work out:

DECLARE @command varchar(1000)

SELECT @command = 'USE ? exec sp_depends ''dbo.table1'''

EXEC sp_MSforeachdb @command

Upvotes: 0

Views: 42

Answers (1)

MrApnea
MrApnea

Reputation: 1946

The problem is probably that dbo.table1 does not exist in all your databases. So you need to validate that the database you are running against contains that table. Try this:

DECLARE @command varchar(1000)

SELECT @command = 'USE ? IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''dbo'' AND  TABLE_NAME = ''table1'')) BEGIN exec sp_depends ''dbo.table1''; END'

EXEC sp_MSforeachdb @command

Upvotes: 0

Related Questions