devxlogo

Get All Tables Referenced by at Least One View

Get All Tables Referenced by at Least One View

The following query will produce a list of tables that are referenced by one or more Views:

SELECT [table “” not found /]

= s.name + N'.' + t.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.tables AS st INNER JOIN sys.schemas AS ss ON st.[schema_id] = ss.[schema_id] CROSS APPLY sys.dm_sql_referencing_entities (QUOTENAME(ss.name) + N'.' + QUOTENAME(st.name), N'OBJECT') AS r INNER JOIN sys.views AS v ON v.[object_id] = r.referencing_id INNER JOIN sys.schemas AS vs ON v.[schema_id] = vs.[schema_id] WHERE st.[object_id] = t.[object_id] )

devx-admin

Share the Post: