T-SQL stored procedure dependencies in a database

Written by

Posted on

I recently needed to get a what tables, views and other stored procedures that every stored procedure in a specific database was referencing. Pretty straight forward, we just need to use some of the system tables/views. There’s lots of examples on the web but here’s my little take on the subject…

SELECT
ss.name AS StoredProcedureSchema
,so.name AS StoredProcedureName

,sed.referenced_database_name AS ReferencingDatabaseName
,sed.referenced_schema_name AS ReferencingSchemaName
,sed.referenced_entity_name AS ReferencingObjectName
,rso.type_desc AS ReferencingObjectType

FROM
sys.sql_expression_dependencies sed

-- join objects so we can get details like the name of 
-- the stored procedure
INNER JOIN
sys.objects so
ON
sed.referencing_id = so.object_id

-- and its schema
INNER JOIN
sys.schemas ss
ON
so.schema_id = ss.schema_id

-- get information about the objects that
-- the stored procedure is referencing
INNER JOIN
sys.objects rso
ON
sed.referenced_id = rso.object_id

-- and their schema as well
INNER JOIN
sys.schemas rss
ON
rso.schema_id = rss.schema_id

WHERE
so.type = 'P' -- just stored procedures

You’ll find that the ‘ReferencingDatabaseName‘ will be NULL if your stored procedures are only referencing objects in the same database.

There’s quite a lot of other information regarding dependencies that can be gleaned from SQL Server, so stay tuned for more little tit bits like this soon… 🙂

%d