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!