Automated way to get every table that's used in any View - INFORMATION_SCHEMA.VIEW_TABLE

I want to see every table in my Azure SQL database that is referenced in a View.

One would think this would work:

select distinct table_name from INFORMATION_SCHEMA.VIEW_TABLE_USAGE

But the table list is incomplete. There are tables missing, which I can manually verify are being called in Views.

Googling, I see others with the same issue, as well as people saying not to use INFORMATION_SCHEMA views, because they're not accurate. Note: Nothing specific to Azure SQL, just in general - posts saying it's inaccurate.

I see references to using sys.objects instead. But if I run

exec sp_helptext 'information_schema.view_table_usage

I see that the inaccurate view, is itself referencing sys.objects

Is there an automated, accurate way to see which tables are being used in a View, within a given database?

CodePudding user response：

You can use sys.sql_expression_dependencies, which is generally more reliable.

SELECT *
FROM sys.sql_expression_dependencies d
WHERE d.referencing_id IN (SELECT v.object_id FROM sys.views)