How to find out the dependencies of stored procedure using sql

Question

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.

EXEC sp_depends @objname = N'usp_Constant_Get_Pvt'

Answer 1

I use this script in a similar situation (don't forget to use 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)