Find all stored procedures that reference another stored procedure

Question

I want to find the all the directly and indirectly references to DB objects list.

I am using below queries but I found the issue.

DECLARE @Search varchar(255)
SET @Search = 'Employee'

; WITH CTE AS 
(
    SELECT DISTINCT
        o.name AS Object_Name, o.type_desc
    FROM 
        sys.sql_modules m 
    INNER JOIN 
        sys.objects o ON m.object_id = o.object_id
    WHERE 
        m.definition LIKE '%' + @Search + '%'

    UNION ALL

    SELECT 
        o.name AS Object_Name, o.type_desc
    FROM 
        sys.sql_modules m 
    INNER JOIN 
        sys.objects o ON m.object_id = o.object_id
    INNER JOIN 
        cte AS c ON c.Object_Name = o.name
)
SELECT * 
FROM Cte 

But I get an error :

Msg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Answer 1

Here the code that will work

DECLARE @Search varchar(255)
SET @Search = 'Employee'

; WITH CTE AS 
(
    SELECT DISTINCT o.name AS Object_Name, o.type_desc
    FROM sys.sql_modules m 
    INNER JOIN sys.objects o ON m.object_id = o.object_id
    WHERE m.definition LIKE '%' + @Search + '%'

    UNION ALL

    SELECT o.name AS Object_Name, o.type_desc
    FROM sys.sql_modules m 
    INNER JOIN sys.objects o ON m.object_id = o.object_id
    , CTE AS c
    WHERE m.definition LIKE '%' + c.Object_Name + '%'
      AND o.name <> c.Object_Name
)
SELECT * 
FROM CTE 
;