Views in SQL Server can be a powerful tool for simplifying complex queries and improving performance. However, there are times when views can negatively impact performance, especially if they are not optimized or if they reference a large number of columns.

In a recent Comprehensive Database Performance Health Check, we encountered a situation where a view was causing significant performance issues. The challenge was to identify which columns were being used in the view. Manually opening the view and listing all the columns proved to be a cumbersome task. To simplify the process, we decided to write a script that could help us identify the columns used in a view.

There are multiple scripts available that can assist in identifying the columns used in any view. Here are two options:

Option 1: Using Information_Schema

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

This script utilizes the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE system view to retrieve information about the columns used in a specific view. By specifying the name of the view in the WHERE clause, you can easily identify the columns used in that view.

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';
	

This script utilizes various Dynamic Management Views (DMVs) to retrieve information about the columns used in a specific view. By joining multiple system tables and views, you can obtain detailed information about the columns, data types, and aliases used in the view.

These scripts provide a simple and efficient way to identify the columns used in a SQL Server view. However, if you are aware of any other scripts or techniques that can accomplish the same task, we would love to hear from you.