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'


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

      @sp_name = NULL
     ,@table   = 'ProductItemDetailsDenorm'

            ;WITH stored_procedures AS (

                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,
            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


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:



--== 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.


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



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

      @sp_name = NULL
     ,@table   = 'ProductItemDetailsDenorm'

   -- Supports Stored Proc, View, User Function, User Table

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

   ; WITH CTE_DependentObjects AS
        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
          UsedByObjectId, UsedByObjectName, UsedByObjectType,
          DependentObjectId, DependentObjectName, DependenObjectType, 
          1 AS Level
       FROM CTE_DependentObjects a
      WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME

       UNION ALL 

          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    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.