Find dependencies of stored procedure in SQL Server

Many a times need arises to find objects a stored procedure depends on. The blogs describes a way to find dependencies of stored procedure in SQL server.

The below query creates a stored procedure.

IF object_id('usp_getpersonaddress') is not null
drop proc usp_getpersonaddress
GO
Create procedure usp_getpersonaddress
AS
SELECT pp.BusinessEntityID,pp.FirstName + ' ' + pp.LastName, 
pa.AddressLine1,pa.City
FROM
Person.Person pp join Person.Address pa 
ON pp.BusinessEntityID = pa.AddressID
GO

The below query lists all the objects the procedure usp_getpersonaddress depends on.

-- find dependency
SELECT referencing_id,OBJECT_SCHEMA_NAME ( referencing_id ) 
	+ '.' + 
    OBJECT_NAME(referencing_id) AS referencing_object_name, 
    obj.type_desc AS referencing_object_type, 
    referenced_schema_name + '.' + 
    referenced_entity_name As referenced_object_name
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS obj ON sed.referencing_id = obj.object_id
WHERE referencing_id =OBJECT_ID('usp_getpersonaddress')
GO

The output of above query is shown below.

find dependencies of stored procedure in sql server

The procedure depends on tables Person.Address and Person.Person which is exactly what we intended to find. This comes handy when altering/dropping tables in a database.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Leave a Reply

Your email address will not be published. Required fields are marked *