CODEWITHSUNDEEP
sqlsql-serversql-server-2012
keyboardandmouse
keyboardandmouse

Reputation: 11

How to find all stored procedures and table from a single stored procedure?

I am working on a database which has does a lot of stored procedures and tables.

One brute force way to know all the stored procedures and tables called by a single stored procedure is by scanning and reading through the code. However, it takes a lot of time.

what is the query to find out all the stored procedures and tables that are called by a main stored procedure?

Upvotes: 1

Views: 1022

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

You can query sys.objects and sys.sql_expression_dependencies tables like this:

SELECT sys.objects.name, sys.objects.type_desc
FROM sys.sql_expression_dependencies 
INNER JOIN sys.objects ON(referenced_id = object_id)
WHERE referencing_id = OBJECT_ID('<Your stored procedure name here>')

Don't forget to replace <Your stored procedure name here> with the actual name of the stored procedure.

Update:
A stored procedure called from another stored procedure will still have a row in sys.sql_expression_dependencies, but will have a null value in the referenced_id column. you can use something like this:

DECLARE @StoredProcedureName sysname = '<Your stored procedure name here>'

SELECT sys.objects.name, sys.objects.type_desc
FROM sys.sql_expression_dependencies 
INNER JOIN sys.objects ON(referenced_id = object_id)
WHERE referencing_id = OBJECT_ID(@StoredProcedureName)

UNION ALL

SELECT procedures.name , 'Stored Procedure' As type_desc
FROM sys.sql_expression_dependencies 
INNER JOIN sys.procedures ON(referenced_entity_name = sys.procedures.name)
WHERE referencing_id = OBJECT_ID(@StoredProcedureName)
AND referenced_id IS NULL

Upvotes: 2

Related Questions