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.
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
