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