Reference of table in all databases of the Server

Question

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

And what exactly does "it did not work out" mean? Did you read the documentation for sp_depends? Now would be a good time to do so. — SMor
– SMor, Commented Jun 23, 2020 at 20:26

MrApnea · Accepted Answer · 2020-06-23 20:34:00Z

0

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

answered Jun 23, 2020 at 20:34

MrApnea

1,9811 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reference of table in all databases of the Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related