Find referenced tables of a stored procedure with sys.sql_expression_dependencies

During fixing one of the performance issues of a stored procedure I wanted to find the tables being used in this complex stored procedure. Doing this manually would have been a nightmare, but there is a system view (Microsoft called them as Object Catalog Views) which we can query to get these results.

If you want to get the referenced tables of a particular stored procedure you can use below query;

SELECT

OBJECT_NAME(referencing_id) AS SP_NAME,

referenced_class_desc,

referenced_database_name,

referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE

OBJECT_NAME(referencing_id) = 'YourStoredProcedureName'

There are quite a bit of additional columns with additional details in the view. You can find more details from this MSDN article.

http://msdn.microsoft.com/en-us/library/bb677315.aspx

This is only available in SQL Server versions 2008 and upwards.