SQL SERVER – How to Identify Columns Used In A View?

The views are very tricky. Sometimes they help performance and sometimes they negatively impact performance. Recently, during Comprehensive Database Performance Health Check, I realized that one of the view was the real performance killer. The challenge was to identify column used in a view. We decided to open the view and list all the columns but that was very cumbersome tasks. Finally, we decided to write a script which can help identify columns used in a view.

There are multiple scripts which can help us to identify which columns are used in any view.

Option 1: Using Information_Schema

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.VIEW_NAME='NameofView'

Option 2: Using DMVs

SELECT
	 v.name AS ViewName,
	 c.name AS ColumnName,
     columnTypes.name AS DataType,
     aliases.name AS Alias
FROM 
sys.views v 
INNER JOIN sys.sql_dependencies d 
    ON d.object_id = v.object_id
INNER JOIN .sys.objects t 
    ON t.object_id = d.referenced_major_id
INNER JOIN sys.columns c 
    ON c.object_id = d.referenced_major_id 
INNER JOIN sys.types AS columnTypes 
    ON c.user_type_id=columnTypes.user_type_id
    AND c.column_id = d.referenced_minor_id
INNER JOIN sys.columns AS aliases
    on c.column_id=aliases.column_id
    AND aliases.object_id = object_id('[SchemaName].[ViewName]')
WHERE
   v.name = 'ViewName';

Well, that’s it! The scripts are very simple and they identify column used in a view.

Let me know if you are aware of any other script which can be helpful to accomplish the same task.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Column, SQL Scripts, SQL Server, SQL View
Previous Post
SQL SERVER – Invalid Object Name ‘master.dbo.spt_values’ in Management Studio
Next Post
SQL SERVER – How to Check if a Column Exists in SQL Server Table?

Related Posts

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.

    Reply
  • Pavan Varma Datla
    October 16, 2017 3:59 pm

    No output generated while using the above two queries, though we have provided the correct view name.

    Reply
  • Entera Source
    July 12, 2018 9:11 pm

    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%’

    Reply
  • Thank you Entera Source, you pointed me in the right way.

    Reply
  • Chris Bailiss
    May 19, 2020 6:48 pm

    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.

    Reply
  • 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 + ‘%’

    Reply

Leave a Reply

Menu
Exit mobile version