CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Prakash S
Prakash S

Reputation: 13

How to find Tables and Views used in stored procedures of different database in sql?

I need to find the tables and views used in stored procedure in which the tables and views may be of different databases.

Is there any way to find?

Eg: if i have sp "dbo.a"

i have to find the tables and views used within this.

The tables and views may be from different databases.

Upvotes: 0

Views: 1182

Answers (2)

GarethD
GarethD

Reputation: 69769

You can use sys.dm_sql_referenced_entities, e.g.

SELECT  *
FROM    sys.dm_sql_referenced_entities(N'dbo.YourProcedure', N'OBJECT');

Upvotes: 1

Aaron Digulla
Aaron Digulla

Reputation: 328624

There is probably no simple way to do this. What you need to do is find the table in your database which contains the code for all the stored procedures or some other way to read them.

Then you need to write an SQL parser which for your database. The parser can then read the source for the stored procedure and produce some data structure (usually an Abstract Syntax Tree) that you can traverse to find all the tables which it references.

Upvotes: 0

Related Questions