儲存過程使用的所有表和使用特定表的所有 sps - 遞歸 cte

我正在編寫一個腳本，該腳本為我提供了使用特定儲存過程的表的列表。

它也適用於儲存過程使用的所有表。

在下面的範例中，所有使用表“ProductItemDetailsDenorm”的儲存過程

USE US15WINMPRODUCT
GO

DECLARE
     @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table
    ,@table   nvarchar(128)

SELECT 
     @sp_name = NULL
    ,@table   = 'ProductItemDetailsDenorm'


           ;WITH stored_procedures AS (

               SELECT 
               schema_name(o.schema_id) as proc_schema,
               o.name AS proc_name, 
               schema_name(oo.schema_id) as table_schema,
               oo.name AS table_name,
               ROW_NUMBER() OVER(PARTITION BY o.name,oo.name ORDER BY o.name,oo.name) AS row
               FROM sysdepends d 
               INNER JOIN sys.objects o ON o.object_id=d.id
               INNER JOIN sys.objects oo ON oo.object_id=d.depid
               WHERE o.type = 'P'

           )

           SELECT proc_schema,
                  proc_name, 
                  table_schema,
                  table_name 
           FROM stored_procedures
           WHERE [row] = 1
           AND ((@sp_name IS NULL) OR (proc_name = @sp_name)) AND ((@table IS NULL) OR (table_name = @table))
           ORDER BY proc_name,table_name 

           --sp_help 'sys.objects'

這給了我下面的列表（部分）：

問題，我正在努力解決的問題是以下情況：

如果在儲存過程 SP1 中有一個使用表 @MyTable 的儲存過程 SP2 怎麼辦？

我也想把它列出來。

我認為解決方案可能是：

應該有一個遞歸 cte 來滿足過程中可能使用表 @table 的過程

但到目前為止我還沒有實現它。

遞歸 CTE 應該有 2 個部分：anchor 和 UNION ALL。我將如何在目前的 CTE 中應用這兩個？

任何提示？

以下腳本來自這個問題 ，它適用於我使用 sql server 2005 的測試（其他儲存過程中的儲存過程）

輸出範例：

這是程式碼：

--=========================================================================

--https://stackoverflow.com/questions/15072445/query-to-recursively-identify-object-dependencies

--== utlGetAllDependentObjectsRecursive - Uses recursive common table
--==     expression to recursively get all the dependent objects as well
--==     as the child objects and child's child objects of a 
--==     Stored Procedure or View or Function.  can be easily modified to 
--==     include all other types of Objects


--Wrote this Stored Procedure below which RECURSIVELY lists all 
--the dependent child objects and child's dependent objects and child's child...etc. 

--The input parameter can be Stored Proc, User Function, View. 

--Can easily be altered to get a Unique List of Column 5, regardless of what Level the Object 
--was called and how deep and by which object.

--COLUMNS

--UsedByObjectId - The parent object that uses the dependent object
--UsedByObjectName - The name of the parent object
--UsedByObjectType - Type of the parent object (P,V,FN)
--DependentObjectId - The child object the parent uses
--DependentObjectName - Name of the child object
--DependentObjectType - Type of the dependent child object (P,V,FN, U)
--Level - How deep, the nested recursive level which the object is used

--=========================================================================



USE US15WINMPRODUCT
GO

DECLARE
     @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table
    ,@table   nvarchar(128)

SELECT 
     @sp_name = NULL
    ,@table   = 'ProductItemDetailsDenorm'




  -- Supports Stored Proc, View, User Function, User Table
declare @PARAM_OBJECT_NAME VARCHAR(500)

select @PARAM_OBJECT_NAME = 'sp1'
--select @PARAM_OBJECT_NAME = 'ProductItemDetailsDenorm'


  ; WITH CTE_DependentObjects AS
   (
       SELECT DISTINCT 
       b.object_id AS UsedByObjectId, 
       b.name AS UsedByObjectName, b.type AS UsedByObjectType, 
       c.object_id AS DependentObjectId, 
       c.name AS DependentObjectName , c.type AS DependenObjectType
       FROM  sys.sysdepends a
       INNER JOIN sys.objects b ON a.id = b.object_id
       INNER JOIN sys.objects c ON a.depid = c.object_id
       WHERE b.type IN ('P','V', 'FN') AND c.type IN ('U', 'P', 'V', 'FN') 
   ),
   CTE_DependentObjects2 AS
   (
      SELECT 
         UsedByObjectId, UsedByObjectName, UsedByObjectType,
         DependentObjectId, DependentObjectName, DependenObjectType, 
         1 AS Level
      FROM CTE_DependentObjects a
     WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME


      UNION ALL 


      SELECT 
         a.UsedByObjectId, a.UsedByObjectName, a.UsedByObjectType,
         a.DependentObjectId, a.DependentObjectName, a.DependenObjectType, 
         (b.Level + 1) AS Level
      FROM CTE_DependentObjects a
      INNER JOIN  CTE_DependentObjects2 b 
         ON a.UsedByObjectName = b.DependentObjectName
   )

   SELECT DISTINCT * FROM CTE_DependentObjects2 
   ORDER BY Level, DependentObjectName    

引用自：https://dba.stackexchange.com/questions/122782