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