CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Michael Leone
Michael Leone

Reputation: 61

SQL Server: Find Stored Procedures called within a procedure

I'm new to a company that makes heavy use of stored procedures (500+). To help learn the system, I was hoping there was an easy way to build a tree type list that shows all stored procedures in the system and which stored procedures they themselves call...thus creating a map of the stored procedures that could be executed. Is there an easy way to do this via a query in SQL Server? Is there a tool/utility that can do this?

For example, I want to see the following type of list without having to painstakingly try and follow the logic in each procedure and manually make a list.

build_house
  -->pour_foundation
    -->order_cement_truck
  -->frame_house
    -->hire_workers
    -->buy_nails_and_hammers
  -->wire_house
    -->hire_electricians
      -->check_certifications
    -->test_wiring

The only thing I've found searching so far is:

http://www.codeproject.com/Articles/10019/Find-Stored-Procedures-called-within-a-procedure

To be clear, I'm looking to pass in / select a stored procedure name and have returned to me all of the stored procedures that it calls/uses.

@JackLock, I downloaded and installed SQL Search, but I don't see how this solves my problem. This tool aids in searching for stored procedures by name, or searching for text in stored procedures, but how does it help me automatically list out all stored procedures that are called from within a particular stored procedure? Maybe I'm missing something? For example, in my example above, I want the ability to run a system query or tool that returns me a list of the stored procedures that are called by whatever stored procedure name I pass it. So in the example, if I give the query or tool "build_house" it returns me the results in the example.

EDIT/UPDATE:

OK, I'd like to try and solve this with a query but need some help. I "think" what I want to do is query the sys.procedures to get the name of all the stored procedures in my system. Once I have them, I want to then pass them into the following query to determine how many stored procedures get called from it:

SELECT referenced_entity_name
FROM sys.dm_sql_referenced_entities (@ProcName, 'OBJECT')

Where @ProcName would get passed in for each row returned by the call to sys.procedures.

What is the most efficient way to do this in t-sql (2008)?

Thanks in advance, Michael

Upvotes: 5

Views: 31694

Answers (3)

chancrovsky
chancrovsky

Reputation: 580

I know it's being along time since the question, however I think I found a workaround for this purpose and I want to share it, I've used an Aaron's Bertrand function to find a pattern within a text and this way of sorting,

Function :

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT pos = Number - LEN(@term) 
      FROM 
      (
        SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, CHARINDEX(@term, @string + @term, Number) - Number)))
        FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
                FROM sys.all_columns) AS n(Number)
            WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
        AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
        ) 
        AS y
    );

Final query:

declare @object_name varchar(1000) = 'stored_procedure_name'
; 
with cte as ( 
    SELECT o.name AS parent_object_name
    , SUBSTRING( m.definition, rs_fn.pos+4, CHARINDEX( ' ' , stuff( m.definition, 1, rs_fn.pos + 4 , '' )) ) as child_object
    , cast(row_number()over(partition by o.object_id order by o.name) as varchar(max)) as [path]
    , 0 as level
    , row_number()over(partition by o.object_id order by o.name) / power(10.0,0) as x
    FROM sys.sql_modules m 
    INNER JOIN sys.objects o ON m.object_id = o.object_id 
    cross apply ( 
        select * 
        from dbo.FindPatternLocation( m.definition, 'EXEC ' ) as res
    ) as rs_fn
    WHERE 1=1
    and o.name like @object_name
    union all 
    SELECT o.name AS parent_object_name
    , SUBSTRING( m.definition, rs_fn.pos+4, CHARINDEX( ' ' , stuff( m.definition, 1, rs_fn.pos + 4 , '' ))  ) as child_object
    , [path] +'-'+ cast(row_number()over(partition by o.object_id order by o.name) as varchar(max))
    , level+1 
    , c.x + row_number()over(partition by o.object_id order by o.name) / power(10.0,level+1)
    FROM sys.sql_modules m 
    INNER JOIN sys.objects o ON m.object_id = o.object_id 
    inner join cte c on c.child_object = o.name
    cross apply ( 
        select * 
        from dbo.FindPatternLocation( m.definition, 'EXEC ' ) as res
    ) as rs_fn
    WHERE 1=1
    -- and o.name like @object_name
) 

select * from cte 
order by x
;

Feel free to use it as you wish.

Upvotes: 0

Venki
Venki

Reputation: 543

You can Enter the particular Procedure name in the below code and check, you will get the particular procedure used in others or not

SELECT OBJECT_NAME(id) 
FROM syscomments 
WHERE [text] LIKE '% procedure_or_function_name %' 
GROUP BY OBJECT_NAME(id)

Upvotes: 8

JackLock
JackLock

Reputation: 1168

You have not mentioned which version of SQL Server you are working on. But there is a free utility (actually SSMS addin) by RedGate called SQL Search. I have it working on SSMS 2005,2008,R2 and 2012

It should solve your problem.

Upvotes: 1

Related Questions