Defining a table specified in a view in SQL Server

Question

How do I know the tables used in a view in SQL Server? Is there a script or tool that can tell me the tables used in the view and can also display a list of fields?

Hope this clears up the issue. Let me know if not.

Please guide! Thanks!

Answer 1

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) .

Answer 2

The easiest way to see the contents of (most) objects:

 sp_helptext blah 

If you replace blah with the name of the object. This will give the real code that created the object. in this case, for example, this can lead to:

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

Answer 3

You can use DISTINCT to get only 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')