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

July 28, 2017

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.

SQL SERVER - How to Identify Columns Used In A View? columnview

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)