BI SQL # 114 : SQL Server DBA Scripts : Find all nested stored procedure – Stored procedure called in another procedure
Hi Geeks,
In this article we are going to cover How to Find all nested stored procedure – Stored procedure called in another procedure.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
How to find which stored procedure had been call in side which stored procedure?
What are relationships of stored procedure with each other?
Description of SQL Script:
This script will display relationships of stored procedure with each other.
A stored procedure can be called from another stored procedure as nested stored procedure. Recently on production server, we were asked for all stored procedures in which other stored procedures are called as nested. Get All Nested Stored Procedures List (Procedures with dependent Procedures)
SQL Script Output Column
SQL Script Code
SELECT * FROM ( SELECT NAME AS ProcedureName ,SUBSTRING(( SELECT ', ' + OBJDEP.NAME FROM sysdepends INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP. OBJECT_ID WHERE obj.type = 'P' AND Objdep.type = 'P' AND sysdepends.id = procs.object_id ORDER BY OBJ.NAME FOR XML PATH('') ), 2, 8000) AS NestedProcedures FROM sys.procedures procs ) InnerTab WHERE NestedProcedures IS NOT NULL
SQL Script Output Screenshot
(Exceled and SQL Output)
Using this Simple test Script
CREATE PROC IamInsideProc AS SELECT 'IamInsideProc' CREATE PROC IamMasterSP AS SELECT 'Now Calling Proc - IamInsideProc' EXEC IamInsideProc EXEC dbo.IamMasterSP
Output will be
User Level to execute
200
Hope you will like How to Find all nested stored procedure – Stored procedure called in another procedure.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 