如何构造显示过程中所有过程的查询?SQL服务器

Question

我需要能够编写一个查询，告诉在一个过程中执行的所有过程，即使有在多个级别中执行的过程。

例如

我有程序1。

步骤1运行步骤2和3。

程序2运行程序4。

有没有办法让我对此进行查询？我试着在网上找，但我真的找不到我要找的东西。

在Management Studio中，如果右键单击一个对象并单击View Dependencies，则有两个选项，"依赖的对象"one_answers"依赖的物体">

我需要查询第二个。

编辑：

我得到了一个查询，这基本上是有效的。我只需要通过程序过滤掉数据。我应该加入哪个表，以便只查看过程？

select  object_name(referencing_id) as referencing_entity_name,
o.type_desc as referencing_desciption,
coalesce(col_name(referencing_id, referencing_minor_id), '(n/a)') as referencing_minor_id,
referencing_class_desc,
referenced_class_desc,
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name,
coalesce(col_name(referenced_id, referenced_minor_id), '(n/a)') as referenced_column_name,
is_caller_dependent,
is_ambiguous
from    sys.sql_expression_dependencies as sed
inner join sys.objects as o
on      sed.referencing_id = o.object_id
where   referencing_id = object_id(N'dbo.npc_sp_par_calc_MinMax')

Answer 1

您需要一个递归CTE。

类似以下(演示)

WITH R
AS (SELECT referenced_id                                          AS referencing_id,
referenced_id                                          AS referenced_id,
CAST(CONCAT('/', referenced_id, '/') AS VARCHAR(8000)) AS referenced_id_path,
OBJECT_SCHEMA_NAME(referenced_id)                      AS referenced_schema_name,
OBJECT_NAME(referenced_id)                             AS referenced_entity_name
FROM   (VALUES (OBJECT_ID('P1'))) V(referenced_id) /*Anchor element*/
UNION ALL
SELECT sed.referencing_id,
CA.referenced_id,
CAST(CONCAT(R.referenced_id_path, CA.referenced_id, '/')AS VARCHAR(8000)),
sed.referenced_schema_name,
sed.referenced_entity_name
FROM   sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o
ON sed.referencing_id = o.object_id
INNER JOIN R
ON sed.referencing_id = OBJECT_ID(CONCAT(R.referenced_schema_name + '.', R.referenced_entity_name))
CROSS APPLY (SELECT OBJECT_ID(CONCAT(sed.referenced_schema_name + '.', sed.referenced_entity_name))) CA(referenced_id)
WHERE  o.type_desc = 'SQL_STORED_PROCEDURE'
AND R.referenced_id_path NOT LIKE CONCAT('%/', CA.referenced_id, '/%'))
SELECT OBJECT_SCHEMA_NAME(referenced_id),
OBJECT_NAME(referenced_id),
referenced_id,
referenced_id_path
FROM   R