CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
xorpower
xorpower

Reputation: 18963

Determine table referenced in a view in SQL Server

How can i get to know the tables used in a view in SQL Server? Is there a script or a tool that could let me know the tables used in a view and can also list down the fields?

Hope this clears the question. Let me know if not.

Please guide! Thanks!

Upvotes: 8

Views: 20726

Answers (5)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

Using below simple query you can know which tables are used in view:

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = 'ViewName'

Upvotes: 1

SumiSujith
SumiSujith

Reputation: 585

This information is available from the INFORMATION_SCHEMA

SELECT * 
    FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu
    JOIN    INFORMATION_SCHEMA.COLUMNS AS c
    ON      c.TABLE_SCHEMA  = cu.TABLE_SCHEMA
    AND     c.TABLE_CATALOG = cu.TABLE_CATALOG
    AND     c.TABLE_NAME    = cu.TABLE_NAME
    AND     c.COLUMN_NAME   = cu.COLUMN_NAME
    WHERE   cu.VIEW_NAME    = 'viewtablename';

Upvotes: 0

R.Alonso
R.Alonso

Reputation: 1065

You can use DISTINCT to get only the tables.

  Select   DISTINCT cols.referenced_entity_name from  
   sys.sql_expression_dependencies objs   outer apply
   sys.dm_sql_referenced_entities (
   OBJECT_SCHEMA_NAME(objs.referencing_id) + N'.' +
   object_name(objs.referencing_id), N'OBJECT' ) as cols where  
   objs.referencing_id = object_id('viewname')

Upvotes: -1

DigCamara
DigCamara

Reputation: 5568

The simplest way to see the content of (most) objects would be:

sp_helptext blah

Where you substitute blah with the name of the object. This would yield the actual code which created the object. in this case, for instance it could result in:

CREATE VIEW blah
AS
  select blah.column1,blah.column2 from blah_table

Upvotes: 1

GSerg
GSerg

Reputation: 78134

select
  cols.*
from
  sys.sql_expression_dependencies objs
  outer apply sys.dm_sql_referenced_entities ( OBJECT_SCHEMA_NAME(objs.referencing_id) + N'.' + object_name(objs.referencing_id), N'OBJECT' ) as cols
where
  objs.referencing_id = object_id('view_name_here')

Reference: sys.dm_sql_referenced_entities (Transact-SQL) .

Upvotes: 8

Related Questions