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… 🙂