Script to get indexes and their included columns

I get increasingly frustrated with the lack of visibility of included columns in management studio and from the system stored procedures sp_...

This is a query that returns all indexes and there key and include columns

select SCHEMA_NAME (o.SCHEMA_ID) SchemaName

   ,o.name ObjectName,i.name IndexName

   ,i.type_desc

   ,LEFT(list, ISNULL(splitter-1,len(list))) Columns

   , SUBSTRING(list, indCol.splitter +1, 100) includedColumns--len(name) - splitter-1) columns

   , COUNT(1) over (partition by o.object_id)

from sys.indexes i

join sys.objects o on i.object_id = o.object_id

cross apply (select NULLIF(charindex('|',indexCols.list),0) splitter , list

              from (select cast((

                           select case when sc.is_included_column = 1 and sc.ColPos = 1 then '|' else '' end +

                                  case when sc.ColPos  > 1 then ', ' else '' end + name

                             from (select sc.is_included_column, index_column_id, name

                                        , ROW_NUMBER() over (partition by sc.is_included_column

                                                             order by sc.index_column_id) ColPos

                                    from sys.index_columns  sc

                                    join sys.columns        c on sc.object_id = c.object_id

                                                             and sc.column_id = c.column_id

                                   where sc.index_id = i.index_id

                                     and sc.object_id = i.object_id ) sc

                    order by sc.is_included_column

                            ,ColPos

                      for xml path (''), type) as varchar(max)) list)indexCols ) indCol

order by SchemaName, ObjectName, IndexName


 


Published 23 February 2010 15:14 by simonsabin

Comments

23 February 2010 16:34 by GrumpyOldDBA

# re: Script to get indexes and their included columns

I have a function I wrote which handles this for me - much easier.

# Script to get indexes and their included columns - Simon Sabin UK … « Script

Pingback from  Script to get indexes and their included columns - Simon Sabin UK … «  Script

24 February 2010 02:46 by SqlServerKudos

# Script to get indexes and their included columns

Kudos for a great Sql Server article - Trackback from SqlServerKudos

# Log Buffer #180: a Carnival of the Vanities for DBAs | The Pythian Blog

Pingback from  Log Buffer #180: a Carnival of the Vanities for DBAs | The Pythian Blog

13 April 2012 22:34 by IN2BI

# SSIS Pattern: Drop- and rebuilt indexes dynamically

Fact- and dimension tables in our data warehouses are queried often. The queries used in our reports and the queries used for processing SSAS cubes create a large workload of select queries. These “select” queries benefit from indexes and will return

13 April 2012 23:17 by IN2BI

# SSIS Pattern: Drop- and rebuilt indexes dynamically

Fact- and dimension tables in our data warehouses are queried often. The queries used in our reports and the queries used for processing SSAS cubes create a large workload of select queries. These “select” queries benefit from indexes and