x

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

more ▼

asked Jul 24, 2014 at 01:43 PM in Default

avatar image

ruancra
1.2k 31 36 45

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Jul 24, 2014 at 08:20 PM

avatar image

Oleg
18.2k 3 7 28

(comments are locked)
10|1200 characters needed characters left

Use SQL Search by Redgate (http://www.red-gate.com/products/sql-development/sql-search/). It's free.

more ▼

answered Jul 25, 2014 at 07:17 AM

avatar image

eghetto
2.2k 18 24 31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x428
x11

asked: Jul 24, 2014 at 01:43 PM

Seen: 449 times

Last Updated: Jul 25, 2014 at 07:18 AM

Copyright 2017 Redgate Software. Privacy Policy