Get the user Stored Procedure dependency in SQL Server

Hello friends, how are you? I am well. Last week I worked on the existing stored procedure and made some modification. As per the requirement, I need to alter the SP and add some input parameter. I made it.

We are getting such type of scenario but the problem arises when this SP is used in other SP. In this case, other SP will fail because in that SP we are not supplying the correct number of the input parameter. We need to update the input parameter in all places.

In prod approx. 1.5k SP is there. It is not possible to open each SP and check dependency. In this case, we need to use below SQL Script to find the Stored Procedure dependency.

select sp.name,sqlm.definition from sys.all_objects sp inner join sys.sql_modules sqlm on sp.object_id=sqlm.object_id where sp.type='P' and sqlm.definition like '% SP name%'

This Script searches the SP definition when the given SP name match it will return the name and definition. We need to have a look at that SP and made the required changes.

Let’s the example.

  

This SP is used on multiple Sp. See below.

   

Let’s see the definition of any SP.

  

In this way, we will get the dependency of the SP.