Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Sep 7, 2017

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] = 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]
  )
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date