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?
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 
