To Identify Columns used in a view, I distinguish references from returned output columns. Those lists answer different questions.
During a database performance health check, we investigated a view involved in slow work. Listing its referenced columns by hand was cumbersome. That prompted the metadata queries.
-- Run in the database containing the view; replace the schema-qualified name.
SELECT referenced_schema_name, referenced_entity_name,
referenced_minor_name AS ReferencedColumn,
is_selected, is_updated, is_select_all
FROM sys.dm_sql_referenced_entities(N'Sales.vIndividualCustomer', N'OBJECT')
WHERE referenced_minor_name IS NOT NULL;
-- These are the view's output columns, a different question:
SELECT column_id, name
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.vIndividualCustomer', N'V')
ORDER BY column_id;The first query reports resolvable referenced columns, including those outside the SELECT output. The second lists returned names and calculations. Source and output column IDs don’t establish an alias mapping. My earlier script incorrectly assumed that relationship.
The original INFORMATION_ approach can still provide a basic column-usage list when filtered by both view schema and name. The revised dependency function replaces the deprecated sys.sql_dependencies example and removes its malformed .sys.objects reference.
Dependency metadata is not a universal lineage engine. Missing or unresolved referenced objects, permissions and cross-database references affect the result. Review the definition when a calculated output or alias needs to be traced precisely. The dependency function requires the relevant SELECT and VIEW DEFINITION permissions.
Reference: Referenced entities and columns.
Related reading
An output alias is not a source-column identity, it is a returned name that needs its own lineage review.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
6 Comments. Leave new
Both queries return zero results on my SQL/Server 2014 instance. I dug a little deeper, and found in option one “INFORMATION_SCHEMA.VIEW_COLUMN_USAGE” was completely empty on the server. And in option two “sys.sql_dependencies” was also completely empty on the server. Since no data exists in either of these objects, the queries yield no result.
I don’t know why and can only guess as to the answer. The instance might not be configured to gather column usage stats or maintain dependencies. What are your thoughts on zero results returned? I am certain the view name is correct when replacing it as a literal in both options provided.
No output generated while using the above two queries, though we have provided the correct view name.
I’m trying to write SQL query to bring all columns for a view, below query does not retrieve any result. Any help is appreciated.
If I expand on View name in SSMS , I can see all the columns with data types
select a.name View_name,b.name column_name
from sys.all_objects a,sys.all_columns b
where a.object_id=b.object_id
and a.type=’V’
and a.name like ‘%_vv%’
Thank you Entera Source, you pointed me in the right way.
The option two query is wrong and should not be used.
It assumes that the same fields are used in the same order in both the table and view, since it is joining on:
INNER JOIN sys.columns AS aliases
on c.column_id=aliases.column_id
column_id is not unique in the database, it is simply an index in the table/view.
If the view has columns in a different order to the table, or if the view only a subset of the columns (e.g. columns 3, 5 and 7 from the table) then this query produces the wrong result.
For tables and views I used:
select c.TABLE_CATALOG
, c.TABLE_SCHEMA
, c.TABLE_NAME
, t.TABLE_TYPE
, c.COLUMN_NAME
, c.DATA_TYPE
, c.CHARACTER_MAXIMUM_LENGTH
, c.COLLATION_NAME
from INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on c.TABLE_CATALOG = t.TABLE_CATALOG and c.TABLE_SCHEMA = t.TABLE_SCHEMA and c.TABLE_NAME = t.TABLE_NAME
where COLUMN_NAME LIKE ‘%’ + @column_name + ‘%’