Jeremy Carter

Awhile back I decided I needed a way to easily look at and compare index definitions. Normally I would go through SSMS and open the indexes I needed through the gui. Then I would flip back and forth between forms but this just was not very efficient. As a result of that experience I came up with the following script which lays out the definitions for all indexes.

select
sch.name+'.'+obj.name [table_name],
i.name [index_name],
i.type_desc,
REPLACE(
(select '['+col.name+']' as [data()]
	from sys.index_columns as k
	inner join sys.columns col
		on k.object_id=col.object_id
		and k.column_id=col.column_id
	where k.object_id = i.object_id
	and k.index_id = i.index_id
	and k.index_id<>0
	and k.is_included_column<>1
	order by key_ordinal, k.column_id
	for xml path(''))
,'] [','], [') as [index_columns],
isnull(
REPLACE(
	(select '['+col.name+']' as [data()]
	from sys.index_columns as k
	inner join sys.columns col
		on k.object_id=col.object_id
		and k.column_id=col.column_id
	where k.object_id = i.object_id
	and k.index_id = i.index_id
	and k.index_id<>0
	and k.is_included_column<>0
	order by key_ordinal, k.column_id
	for xml path(''))
,'] [','], [')
,'') as [index_included_columns],
ISNULL(i.filter_definition,'') as filter_definition,
is_unique,
is_primary_key,
is_unique_constraint,
fill_factor,
is_padded,
allow_row_locks,
allow_page_locks,
ignore_dup_key,
ISNULL(data_compression_desc,'') as data_compression_desc,
no_recompute
from sys.indexes as i
inner join sys.objects obj
	on i.object_id=obj.object_id
inner join sys.schemas sch
	on obj.schema_id=sch.schema_id
inner join sys.partitions part
	on i.object_id=part.object_id
	and i.index_id=part.index_id
inner join sys.stats stats
	on i.object_id=stats.object_id
	and i.index_id=stats.stats_id
where i.name is not null
and obj.type<>'S'
and sch.name<>'sys'
--and sch.name='HumanResources'
--and obj.name='Employee'
order by table_name, type_desc, index_columns

The output of the script will look like this.

-Jeremy