How to find out the dependencies of stored procedure using sql

I want a script which will show the dependencies of stored procedure in database. Actually when we manually do view dependency it will take a lot of time I have more than 500 stored procedures. So, I wanted to know that these stored procedures are used in database or not so that I can remove the useless stored procedure.

sp_depends is not showing all results because I want all objects that depends on this stored procedure 'usp_Constant_Get_Pvt' and objects on which it depends.

sp_depends

EXEC sp_depends @objname = N'usp_Constant_Get_Pvt'


2 Answers
2

I use this script in similar situation. Don't forget to put the schema name.

--
DECLARE
@sp nvarchar(100)
SET @sp = N'dbo.usp_Constant_Get_Pvt'

-- Objects that depends on [@sp]
SELECT
referencing_schema_name,
referencing_entity_name
FROM sys.dm_sql_referencing_entities(@sp, 'OBJECT')

-- Objects on which [@sp] depends
SELECT
referenced_schema_name,
referenced_entity_name
FROM sys.dm_sql_referenced_entities(@sp, 'OBJECT')

SELECT
referenced_schema_name,
referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(@sp)


Try with the Following Query

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


Please refer This link for more detailed information

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.