How to get the list of all dependent procedures?

[A M SOMAN]

Hi, Is there any way to find all stored procedures which are being called inside the main procedure? This can go deeper at last level inside the main one. I have tried the available option for "right click" > "View Dependancies" but it does not show the procedures called by EXEC statement. Please correct me if I am wrong here. My problem is - I have a procedure which calls multiple other procedures inside it. Also these called procedures again might have called other procedures inside them. Now I need to find a list of all such dependent procedures for my main procedure. Thanks in advance for your inputs! Anil

[Henry Minute]

There is an answer to this on SQLServerCentral.com here[^]. You might have to sign up to view it, can't remember, but it is a reputable and useful site.

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[Md Marufuzzaman]

Hi Try the following: Link 1 : sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/[^] Or

DECLARE @StringToSearch NVARCHAR(MAX)
SET @StringToSearch = 'ENTER_SP_TO_SEARCH'

SELECT
[name],
(
SELECT
OBJECT_DEFINITION(obj2.object_id) AS [text()]
from sys.all_objects obj2
where obj2.object_id = obj1.object_id
FOR XML PATH(''), TYPE
) AS Obj_text,
[type] as ObjType
FROM sys.all_objects obj1
WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @StringToSearch + '%'

Thanks Md. Marufuzzaman

---

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.