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)