A SQL query to list all stored procedures executed in a parent stored procedure

Question

I have multiples stored procedures in an Azure SQL database that execute multiple stored procedures.

For example:

CREATE PROCEDURE [DBO].[PARENT] AS
BEGIN
    EXEC [DBO].[CHILD1];
    EXEC [DBO].[CHILD2];
END

What I want to do is write a query that lists all parent stored procedures that I have in one column and in the second column lists all child stored procedures called.

Is there a way to do so?

Thanks in advance

Does this answer your question? [Find all references to an object in an SQL Server database](https://stackoverflow.com/questions/3681291/find-all-references-to-an-object-in-an-sql-server-database) specifically the second answer referring to `dm_sql_referencing_entities` — Charlieface, Jan 10 '21 at 12:40
@PeterSmith Does `dm_sql_referencing_entities` not work as I thought? — Charlieface, Jan 10 '21 at 13:06
@PeterSmith OP is using standard EXEC statements, no dynamic sql. See this [fiddle](http://sqlfiddle.com/#!18/3a29b/5) to prove it works — Charlieface, Jan 10 '21 at 13:53
@Charlieface That's excellent - +1 below from me and thanks for the clarification — Peter Smith, Jan 10 '21 at 13:57

Charlieface · Accepted Answer · 2021-01-10T14:00:56.583

You can use the DMVs sys.procedures and sys.dm_sql_referencing_entities to get this info:

SELECT
    parent = referencing_schema_name + '.' + r.referencing_entity_name,
    child = QUOTENAME(OBJECT_SCHEMA_NAME(child.schema_id)) + '.' + QUOTENAME(child.name)
FROM sys.procedures AS child
JOIN sys.dm_sql_referencing_entities
    (QUOTENAME(OBJECT_SCHEMA_NAME(child.schema_id)) + '.' + QUOTENAME(child.name),
    'OBJECT') AS r;

Pouyan Asadi · Answer 2 · 2021-01-10T14:12:17.517

the sp_depends displays information about database object dependencies.

The following example lists the Procedures that depends on dbo.spSample_ProcedureName

EXEC sp_depends @objname = N'dbo.spSample_ProcedureName';

You can do some change on sp_depends and extract what you needed.

I'd rather to edit my answer, because i think this is a useful question. So i wrote a full query to display the result. I've test this query and it was successful.

DECLARE @Res AS TABLE (SP_Names NVARCHAR(776), Dependencies NVARCHAR(MAX))

SELECT [name]
INTO #t_sp 
FROM sys.procedures


 WHILE EXISTS (SELECT [name] FROM #t_sp)
 BEGIN
 
 DECLARE @objname nvarchar(776) = (SELECT TOP(1) [name] FROM #t_sp)

 declare @objid int         -- the id of the object we want
 declare @found_some bit            -- flag for dependencies found
 declare @dbname sysname


SELECT @dbname = parsename(@objname,3)

    if @dbname is not null and @dbname <> db_name()
        begin
            raiserror(15250,-1,-1)

        end

    --  See if @objname exists.
    select @objid = object_id(@objname)
    if @objid is null
        begin
            select @dbname = db_name()
            raiserror(15009,-1,-1,@objname,@dbname)

        end



    --  Now check for things that depend on the object.
    if exists (select *
            from sysdepends
                where depid = @objid)
    begin
            raiserror(15460,-1,-1)
        INSERT INTO @Res
        (
            SP_Names,
            Dependencies
        )
        
        SELECT  distinct 'name' = (s.name + '.' + o.name), @objname
            --type = substring(v.name, 5, 66)               -- spt_values.name is nvarchar(70)
                from sys.objects o, master.dbo.spt_values v, sysdepends d,
                    sys.schemas s
                where o.object_id = d.id
                    and o.type = substring(v.name,1,2) collate catalog_default and v.type = 'O9T'
                    and d.depid = @objid
                    and o.schema_id = s.schema_id
                    and deptype < 2

        select @found_some = 1
    end

    DELETE #t_sp WHERE [name] = @objname


END


DROP TABLE #t_sp

SELECT * FROM @Res
ORDER BY SP_Names

A SQL query to list all stored procedures executed in a parent stored procedure

2 Answers2