列出sql server中存储过程中的存储过程

使用此项查找依赖项

EXEC sp\u依赖于@objName=N'Stored\u Procedure\u Name'

您可以使用information\u架构。与系统相结合的例程。sql\u expression\u dependencies-但您需要以递归方式使用它。

with recursedProcs(code_database, ancestorName, object, Recursion, procpath)    
as 
(
    select isr.specific_catalog as code_database, isr.[specific_name] as ancestorName, cast(isr.specific_name as varchar(200)) as object, 1 as Recursion, cast(isr.specific_name as varchar(200)) as procpath
    from information_schema.routines isr with (nolock)
    union all 
    select p.code_database, p.ancestorName,  cast(d.referenced_entity_name as varchar(200)), p.recursion + 1, cast(p.procpath + '/' + cast(d.referenced_entity_name as varchar(200)) as varchar(200)) 
    from recursedProcs p
    inner join sys.sql_expression_dependencies d on d.referencing_id = OBJECT_ID(p.object)
    where p.recursion < 20
    and d.referenced_id <> object_id(p.object)
)
select * from recursedProcs

这将递归数据库中的每个过程,通过子过程,最后递归到过程所依赖的表。从这一点出发,您可以对其进行优化,以满足您的需要。(如果子过程的深度超过20级,还需要添加maxrecursion选项并删除<20谓词)

sp\U Dependes不再推荐使用,目前确实有能力给出错误的答案。