我如何...如何从嵌套存储过程中获得完整的依赖过程 [英] How do i...how can I get complete dependent procedures from nested stored procedures also
问题描述
是否可以编写SQL查询/过程来识别存储过程依赖项的完整列表？我只对识别其他存储过程感兴趣，而且我也不想限制嵌套的深度。例如，如果A调用B，调用C，调用D，我希望B，C和D报告为A的依赖关系。
什么我试过了：
i在数据库中只发现了程序的依赖sp但是我需要依赖sp的s; s
Is it possible to write a SQL query/procedure to identify a complete list of a stored procedures dependencies? I'm only interested in identifying other stored procedures and I'd prefer not to limit the depth of nesting that it gets too either. For example, if A calls B, which calls C, which calls D, I'd want B, C and D reported as dependencies for A
What I have tried:
i found only dependent sp's of procedueres in a db but i need dependents of dependent sp;s
推荐答案
这样的事情应该有效：
Something like this should work:
WITH cteDependencies As
(
SELECT
D.referencing_id,
D.referenced_id
FROM
sys.sql_expression_dependencies As D
WHERE
D.referencing_id = OBJECT_ID(@ProcedureName)
And
D.referenced_id != D.referencing_id
UNION ALL
SELECT
D.referencing_id,
D.referenced_id
FROM
cteDependencies As R
INNER JOIN sys.sql_expression_dependencies As D
ON D.referencing_id = R.referenced_id
WHERE
D.referenced_id != D.referencing_id
)
SELECT
OBJECT_SCHEMA_NAME(D.referencing_id) As referencing_schema_name,
OBJECT_NAME(D.referencing_id) referencing_entity_name,
OBJECT_SCHEMA_NAME(D.referenced_id) As referenced_schema_name,
OBJECT_NAME(D.referenced_id) referenced_entity_name,
O.type_desc
FROM
cteDependencies As D
INNER JOIN sys.objects As O
ON O.object_id = D.referenced_id
WHERE
O.type In ('P', 'PC', 'X', 'FN', 'FS', 'TF', 'IF', 'FT')
;
MSDN上记录了类型代码列表： sys.objects（Transact-SQL） [ ^ ]
解决方案1的替代方案 - 仍然使用CTE但也是一个（可怕的）游标（但它在一个表变量上也不错）所以你可以得到所有 SP的依赖关系
我创建了4个存储过程来测试它...
SP_A调用
SP_B调用
SP_C哪个只是打印出来的东西
SP_D调用SP_B然后调用SP_A（即循环）
我得到了所有存储过程的列表以及它们被调用的那些，比如这个
An alternative to solution 1 - still using a CTE but also a (dreaded) cursor (but it's on a table variable so not too bad) so you can get the dependencies for all of the SPs
I created 4 stored procedures to test this out...
SP_A calls
SP_B calls
SP_C which just prints something out
SP_D which calls SP_B then SP_A (i.e. circular)
I got the list of all stored procedures and which ones they were called by, like this
declare @sps table(id int identity(1,1), [name] varchar(max))
declare @deps table([name] varchar(max), refby varchar(max))
insert into @sps ([name]) select specific_name from information_schema.routines
where routine_type='PROCEDURE'
declare c cursor for
select [name] from @sps
declare @name varchar(max)
open c
fetch next from c into @name
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @deps SELECT @name, referencing_entity_name
from sys.dm_sql_referencing_entities(@name, 'OBJECT')
fetch next from c into @name
END
CLOSE c
DEALLOCATE c
我的测试数据给了我
for my test data that gave me
3 SP_C SP_B
4 SP_B SP_A
4 SP_B SP_D
5 SP_A SP_D
6 SP_D NULL
然后我将其输入以下内容以走层次结构
I then fed this into the following to "walk" the hierarchy
-- Outer join is not allowed in the recursive part of a CTE so ...
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
select id, S.name, refby
into #temp
from @sps S
LEFT OUTER JOIN @deps D ON S.name=D.name
;WITH cte AS
(
SELECT name, refby
, 1 as RLevel
, MtoE = CAST(isnull(name,'') AS VARCHAR(MAX))
from #temp
WHERE refby IS NULL
UNION ALL
SELECT e.name, e.refby
, RLevel + 1
, MtoE = MtoE + '/' + CAST(e.name AS VARCHAR(MAX))
FROM #temp e
INNER JOIN cte ecte ON ecte.name = e.refby
)
SELECT *
FROM cte EC
ORDER BY [name], RLevel
哪个给了我结果
Which gave me the results
SP_A SP_D 2 SP_D/SP_A
SP_B SP_D 2 SP_D/SP_B
SP_B SP_A 3 SP_D/SP_A/SP_B
SP_C SP_B 3 SP_D/SP_B/SP_C
SP_C SP_B 4 SP_D/SP_A/SP_B/SP_C
SP_D NULL 1 SP_D
最后一列显示通过上述调用的存储过程的路径。请注意，每个SP有多个行。
The final column shows the path to the stored procedure through the calls as described above. Note there are multiple rows per SP.
这篇关于我如何...如何从嵌套存储过程中获得完整的依赖过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
