Determine table referenced in a view in SQL Server

Question

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!

Bamboo

possible duplicate of Text search in stored proc SQL Server. you can find dependencies but not get the field list too – Bevash

This is not an exact duplicate of Text search in stored proc SQL Server. Separate answer exists. Please don't vote for close. – Carlisle

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

Carlisle

GSerg; thanks for that. From where did you got that script? Because the script returns null for all the columns – Bamboo

@Xorpower I just wrote it, having looked up the required sysview in BOL. It does work, table names are in referenced_entity_name and column names are in referenced_minor_name. If it doesn't for you, try providing a more qualified name of the view (such as dbo.view_name) or recompiling the view before examining it. – Carlisle

This works only if a view has another view being referenced into it. The reference_entity_name shows the another view being referred in my case and not the table name. Yes column name are in ref_minor_name. – Bamboo

@Xorpower Then there are other issues with your database. The query does show all referenced tables and views. To double-check, download SQL Manager for SQL Server (free version), open up your view and switch to Dependecies tab. – Carlisle

Why not just select referenced_entity_name,referenced_minor_name from sys.dm_sql_referenced_entities ( 'dbo.foo', N'OBJECT' ) where referenced_minor_name is not null? The OUTER APPLY just seems to bring in additional rows as it is repeatedly called with the same values. – Ornithology

Martin, your script actually works! But is there a way to know whether the entity name is a table or a view? I have a view that references to some other view. – Bamboo

GSerg: the dependency tab in the SQL Manager comes blank. – Bamboo

@Xorpower That is the problem. It shouldn't. Try executing sp_refreshview for all views. – Carlisle

@MartinSmith You're correct, I took that out from a bigger script that analysed multiple objects at once. Still, the data should be the same (not true, but true enough). If outer apply yields no data whereas a direct call does, then sys.sql_expression_dependencies doesn't contain any rows for which outer apply could have been called. That's an issue. – Carlisle

Answer 2

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

Middlemost

Answer 3

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'

Zobias

Answer 4

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';

Emmett

Answer 5

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

Lactiferous