Which Tables are Referenced by a View?

Sometimes you want to find all the tables that are included in the code of a stored procedure, or maybe you need a list of all the objects (tables, views, functions) that are included in the code of some view.

You can use the View Dependencies feature in SSMS, but it's a manual operation and doesn't always return all the results that you would expect. Instead, you can write your own query to retrieve object dependencies using sys.sql_expression_dependencies.

This catalog view contains one row for each dependency on a user-defined entity in the current database. For example, when a table is referenced in the definition of a view, then the view will appear in this catalog view as the referencing entity, and the table will appear as the referenced entity.

This catalog view also covers cross-database and even cross-server references. It also covers column references. When the referenced entity cannot be resolved to an object ID in the local database (e.g. cross-database reference), then you can still get the string of each element of the four-part name of the object, if it exists.

I published a very simple script to query for all the objects referenced by a specific object in the local database (e.g. a stored procedure). It's in our Madeira Toolbox. You can tweak the script to return all kinds of dependencies. Have fun!



Get New posts delivered straight to your inbox

Thank you for subscribing!