“Key” columns values are used for sorting the index and they exist at the node level and the leaf level of the balanced tree structure. “Included” columns are not used for sorting the index and they only exist at the leaf level.

--set the table name below to search for a single table
--leave blank to return indexes for all tables
declare @table_name as varchar(255) = ''

select 
	schema_name(t.schema_id) + '.' + t.name as [table],
    i.name as [index],
    c.name as [column],
	ic.is_included_column
from 
    sys.indexes as i
	join sys.index_columns as ic 
		on ic.object_id = i.object_id 
		and ic.index_id = i.index_id
	join sys.columns as c 
		on c.object_id = ic.object_id 
		and c.column_id = ic.column_id
	join sys.tables as t 
		on t.object_id = c.object_id
where
	--if @table_name is null or empty then do not filter
	coalesce(ltrim(@table_name), '') = '' 
	or lower(t.name) = lower(@table_name)
order by 
	i.index_id,
	ic.index_column_id

Advertisements