如何使用sql找出存储过程的依赖关系 [英] How to find out the dependencies of stored procedure using sql

问题描述

我想要一个脚本来显示数据库中存储过程的依赖关系.实际上，当我们手动执行视图依赖时，我有 500 多个存储过程需要很多时间.所以，我想知道这些存储过程是否在数据库中使用，以便我可以删除无用的存储过程.

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 没有显示所有结果，因为我想要依赖于这个存储过程usp_Constant_Get_Pvt"的所有对象和它依赖的对象.

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'

推荐答案

我在类似情况下使用这个脚本(不要忘记使用架构名称):

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)

这篇关于如何使用sql找出存储过程的依赖关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！