CODEWITHSUNDEEP
sql-serverstored-proceduresnested
idelix
idelix

Reputation: 145

Nested stored procedures chain

I have this SQL Server with a lot of stored procedures spread across all databases and I'm looking for a way to find the how those nested stored procedures are chained with each other, so basically to see which stored procedure is running which.

Wonder if anyone who had the same problem and found a way to get such information in some painless way rather than opening each stored procedure and checking what other stored procedure it is running.

Thanks

Upvotes: 5

Views: 2330

Answers (2)

user1499112
user1499112

Reputation:

Approach #1

Right click on the table name and select "View Dependencies" as shown below we are looking at dependencies for the Employee table.

enter image description here

Approach #2

SELECT 
        routine_name, 
        routine_type 

FROM    INFORMATION_SCHEMA.ROUTINES

WHERE   ROUTINE_DEFINITION LIKE '%Your Object Name%'

Approach #3

EXEC sp_depends 'Your Object Name'

Approach #4

SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Your Object Name', 'OBJECT');

Upvotes: 4

Dani
Dani

Reputation: 1276

Depending on what information you want, you can query the sys.sql_expression_dependencies catalog view or use the sys.dm_sql_referenced_entities or sys.dm_sql_referencing_entities dynamic management functions.

Information on these, including various examples of SQL queries for both direct and indirect dependencies (chained dependencies) can be found here: Reporting SQL Dependencies.

Upvotes: 0

Related Questions