SQL Server: Find Stored Procedures called within a procedure

Question

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

Answer 1

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)

Answer 2

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.