Retrieving Dependent Stored Procedures

I inherited many very large stored procedures with nested stored procedures inside. I wanted to create a stored procedure that I could run to show me all the dependencies for a particular stored procedure - listing all tables and nested stored procedures within it.

I am using the following code, but it only gives me top level dependencies, not anything from nested stored procedures.

CREATE TABLE #TEMP_ROADMAP
(
    TBL_OBJECT_NAME VARCHAR(50),
    TBL_TYPE VARCHAR(20),
    TBL_PARENT VARCHAR(20),
    PROCNAME VARCHAR(50)
)

DECLARE @PARENTPROC VARCHAR(100)
SET @PARENTPROC = '[DBO].[RULE_PROC_1]' 

INSERT INTO #TEMP_ROADMAP (TBL_OBJECT_NAME,TBL_TYPE)
    SELECT DISTINCT
        [OBJECT_NAME] = SCHEMA_NAME(O.[SCHEMA_ID]) + '.' + O.NAME, O.TYPE_DESC
    FROM    
        SYS.DM_SQL_REFERENCED_ENTITIES (@PARENTPROC, 'OBJECT') D
    JOIN 
        SYS.OBJECTS O ON D.REFERENCED_ID = O.[OBJECT_ID]
    WHERE 
        O.[TYPE] IN ('U', 'V', 'P', 'IF', 'FN')

   UPDATE #TEMP_ROADMAP
   SET TBL_PARENT = 'PARENT',
       PROCNAME = @PARENTPROC
   WHERE TBL_OBJECT_NAME IS NOT NULL

   SELECT * FROM #TEMP_ROADMAP

   DROP TABLE #TEMP_ROADMAP

      

Output:

TBL_OBJECT_NAME  TBL_TYPE               TBL_PARENT       PROCNAME
dbo.RULE_PROC_2  SQL_STORED_PROCEDURE   PARENT           [DBO].[RULE_PROC_1]
dbo.TEST_TABLE_1 USER_TABLE             PARENT           [DBO].[RULE_PROC_1]

      

Can this be done when I can get the tables and name of other nested stored procedures in this parent procedure?

+3


source to share


1 answer


Sorry if I misunderstood you, but I think you need this: -



   EXECUTE SP_DEPENDS <PROCNAME>

      

0


source







All Articles