Скачать или смотреть How to Automatically Find Every Table Used in Any View in Your Azure SQL Database

Discover a reliable method to accurately list all tables referenced in views within your Azure SQL database using the `sys.sql_expression_dependencies` table.
---
This video is based on the question https://stackoverflow.com/q/72635917/ asked by the user 'bbb0777' ( https://stackoverflow.com/u/8570140/ ) and on the answer https://stackoverflow.com/a/72638528/ provided by the user 'Charlieface' ( https://stackoverflow.com/u/14868997/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Automated way to get every table that's used in any View - INFORMATION_SCHEMA.VIEW_TABLE_USAGE is incomplete

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

Managing views in an Azure SQL database can sometimes be tricky, especially when you want to retrieve all underlying tables associated with those views. If you've ever attempted to use INFORMATION_SCHEMA.VIEW_TABLE_USAGE, you might have found it incomplete. Many users face the same challenge: identifying all the tables referenced by different views in their database.

In this guide, we'll explore the limitations of INFORMATION_SCHEMA.VIEW_TABLE_USAGE and explain how you can leverage an alternative approach—using the sys.sql_expression_dependencies table—to get a complete and accurate list of all tables in views.

The Problem with INFORMATION_SCHEMA

Incomplete Table Listings

When querying for tables that are referenced in views using the standard INFORMATION_SCHEMA approach, such as:

[[See Video to Reveal this Text or Code Snippet]]

Many users have found this method to yield incomplete results. This is critical for database management and understanding dependencies, yet many continue to encounter discrepancies.

The General Consensus

After some research, it's become apparent that many professionals advise against reliance on INFORMATION_SCHEMA views for accurate data. While they provide a level of information, the underlying data structures aren’t always fully accurate or complete, leading to gaps when you attempt to audit or manage your views effectively.

A Reliable Solution: Utilizing sys.sql_expression_dependencies

To get a more accurate representation of the tables used in your views, consider leveraging the system view sys.sql_expression_dependencies. This method is generally regarded as more reliable due to its comprehensive nature.

Querying with sys.sql_expression_dependencies

To find all tables used in views of your Azure SQL database, run the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Query

SELECT * FROM sys.sql_expression_dependencies d: This part of the query selects all columns from the sys.sql_expression_dependencies view, which contains information about dependencies between SQL objects.

WHERE d.referencing_id IN (SELECT v.object_id FROM sys.views): Here we filter the dependencies that are specifically referencing views. object_id from sys.views helps us to isolate only the dependencies that pertain to views, ensuring our result set only includes relevant data.

Advantages of Using this Approach

Comprehensive Data: This method accounts for all possible dependencies within your SQL Server environment.

Less Error-Prone: As mentioned, it is generally more accurate than the INFORMATION_SCHEMA alternatives.

Automated Reporting: By using this system view, you can easily get a report of all dependencies without manual verification.

Conclusion

By utilizing sys.sql_expression_dependencies, you can automate and accurately retrieve every table that is used in any view in your Azure SQL database. This method not only saves time but also ensures that you have a complete understanding of how your data is interconnected.

If you’ve struggled with incomplete table listings in views, give this approach a try. It’s a simple and effective solution for a common problem in SQL database management.