question

ruancra avatar image
ruancra asked

Search for tables referenced in proc cross databases.

Hi all Is there a way to return a list of distinct table names used in all procedures for a specific database? Some procedures references tables on the same server but a different database. sp_depends only returns table names located in the local database. Using SQL 2014. Thanks
tsqlsql2014
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
The dynamic management function named ** sys.dm\_sql_referenced\_entities** will return the list of referenced objects even if they are from different databases of the same instance: select * from sys.dm_sql_referenced_entities('dbo.your_proc_name, 'object') By the way, the **sp\_depends** is not always reliable and therefore, it is best to avoid using it if at all possible. For example, if you have a procedure which depends on some table and then drop and re-create the table with the same name, the procedure will still execute just fine, will still depend, but there will be nothing returned by calling sp\_depends. Here is the small snippet to demonstrate it: -- create a table and populate it with values create table dbo.TheTable (SomeColumn int not null); go insert into dbo.TheTable values(1), (2), (3); go -- create a procedure which depends on this table create proc dbo.usp_SomeProc as select * from dbo.TheTable; go -- check sp_depends to make sure it has a record in it -- identifying TheTable as referenced object sp_depends 'dbo.usp_SomeProc'; go -- results name type updated selected column ------------- ---------- --------- --------- ---------- dbo.TheTable user table no yes SomeColumn -- now drop and recreate the table drop table dbo.TheTable; go -- re-create a table againg (same name) and populate it with values create table dbo.TheTable (SomeColumn int not null); go insert into dbo.TheTable values(1), (2), (3); go -- now the procedure still exists, still depends, still returns -- the same data, but there is no row returned by sp_depends :( sp_depends 'dbo.usp_SomeProc'; go -- results Object does not reference any object, and no objects reference it. -- clean up: drop proc dbo.usp_SomeProc; go drop table dbo.TheTable; go Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.