Sql-server – all tables used by a stored procedure and all sps that use a particular table – recursive cte

dependenciessql serversql-server-2005stored-procedures

I am working on a script that gives me a list of tables that use a specific stored procedure.

It also works on all tables used by a stored procedure.

on the example below, all stored procedures that use the table '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'

This gives me the list below (partial):

enter image description here

The question, the bit I am struggling with is the following situation:

what if inside the stored procedure SP1 there is a stored procedure SP2 that uses the table @MyTable?

I want that to be listed as well.

I thought the solution could be:

there should be a recursive cte to cater for procedures inside
procedures that might use the table @table

but so far I have failed to implement it.

recursive CTEs should have 2 parts: anchor and UNION ALL.
how would I apply these two in the current CTE?

any hints?

enter image description here

Best Answer

the following script comes from this question and it has worked fine for my tests using sql server 2005 (stored procedures inside other stored procedures)

example of output: enter image description here

this is the code:

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

--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
Related Question