Get Index Columns

DROP FUNCTION dbo.udf_GetIndexColumns

GO

CREATE FUNCTION dbo.udf_GetIndexColumns(@TableName SYSNAME = NULL)

RETURNS @udf_IndexColumns TABLE

(

DatabaseName SYSNAME NULL,

TableSchema SYSNAME NULL,

TableName SYSNAME NULL,

IndexName SYSNAME NULL,

IndexType VARCHAR(20),

IndexColumns VARCHAR(1000),

IndexColumns_SO VARCHAR(1000),

IncludedColumns VARCHAR(1000),

IncludedColumns_SO VARCHAR(1000),

StatisticsLastUpdated DATETIME,

FileGroupName SYSNAME NULL,

Fill_Factor INT,

IsPadded INT,

NoRecompute INT,

AllowRowLocks INT,

AllowPageLocks INT

)

AS

/***************************************************************

Purpose: Lists the indexes and their columns

Also lists hepa tables, but as every table has a heap, these

are restricted to tables with no other index definitions

The following DMVs are used:

sys.indexes - list all index details in the db

sys.index_columns - list all index column details in the db

sys.tables - list all table details in the db

sys.all_columns - list all columns detail in the db

Due to the use of CTE, this is valid for SQL 20065 and greater

For conversion to SQL 2000, the CTE code could be used to populate a temp table

Author: Unknown

History: 7 Nov 2010 - Initial Issue

6 Jul 2012 - ChillyDBA - Converted to function and enhanced with schema and stats date

7 Jul 2012 - ChillyDBA - Augmented results to encompass included columns

plus versions of the columns lists with sort order, plus

some essential index creation flags.

23 Jun 2014 - ChillyDBA - added [] around column names returned to insulate against special characters or spaces in names.

****************************************************************/

BEGIN

WITH AllIndexes_CTE

(

TableSchema,

TableName,

IndexName,

IndexType ,

IndexColumns,

IndexColumns_SO,

IncludedColumns,

IncludedColumns_SO,

StatisticsLastUpdated,

FileGroupName,

Fill_Factor,

IsPadded,

NoRecompute,

AllowRowLocks,

AllowPageLocks

)

AS

-- identify indexes of all types

(

SELECT

SCHEMA_NAME(Tab.schema_id) AS TableSchema,

Tab.[name] AS TableName,

IND.[name] AS IndexName,

IND.Type_Desc AS IndexType,

SUBSTRING (

(

SELECT ', [' + AC.name + ']'

FROM sys.tables AS T

INNER JOIN sys.indexes I

ON T.OBJECT_ID = I.[object_id]

INNER JOIN sys.index_columns IC

ON I.OBJECT_ID = IC.[object_id]

AND I.index_id = IC.[index_id]

INNER JOIN sys.all_columns AC

ON T.OBJECT_ID = AC.[object_id]

AND IC.column_id = AC.[column_id]

WHERE Ind.OBJECT_ID = i.[object_id]

AND ind.index_id = i.index_id

AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR XML PATH('')

), 2, 8000

) AS IndexColumns,

SUBSTRING (

(

SELECT ', ['

+ AC.name

+ CASE

WHEN ic.is_descending_key = 1 THEN '] DESC'

ELSE '] ASC'

END

FROM sys.tables AS T

INNER JOIN sys.indexes I

ON T.OBJECT_ID = I.[object_id]

INNER JOIN sys.index_columns IC

ON I.OBJECT_ID = IC.[object_id]

AND I.index_id = IC.[index_id]

INNER JOIN sys.all_columns AC

ON T.OBJECT_ID = AC.[object_id]

AND IC.column_id = AC.[column_id]

WHERE Ind.OBJECT_ID = i.[object_id]

AND ind.index_id = i.index_id

AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR XML PATH('')

), 2, 8000

) AS IndexColumns_SO,

SUBSTRING (

(

SELECT ', [' + AC.name + ']'

FROM sys.tables AS T

INNER JOIN sys.inddexes I

ON T.OBJECT_ID = I.[object_id]

INNER JOIN sys.index_columns IC

ON I.OBJECT_ID = IC.[object_id]

AND I.index_id = IC.[index_id]

INNER JOIN sys.all_columns AC

ON T.OBJECT_ID = AC.[object_id]

AND IC.column_id = AC.[column_id]

WHERE Ind.OBJECT_ID = i.[object_id]

AND ind.index_id = i.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR XML PATH('')

), 2, 8000

) AS IncludedColumns,

SUBSTRING (

(

SELECT ', [' + AC.name + ']' -- no sort order on an included column

FROM sys.tables AS T

INNER JOIN sys.indexes I

ON T.OBJECT_ID = I.[object_id]

INNER JOIN sys.index_columns IC

ON I.OBJECT_ID = IC.[object_id]

AND I.index_id = IC.[index_id]

INNER JOIN sys.all_columns AC

ON T.OBJECT_ID = AC.[object_id]

AND IC.column_id = AC.[column_id]

WHERE Ind.OBJECT_ID = i.[object_id]

AND ind.index_id = i.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR XML PATH('')

), 2, 8000

) AS IncludedColumns_SO,

STATS_DATE(TAB.OBJECT_ID, IND.Index_ID) AS StatisticsLastUpdated,

fg.name AS FileGroupName,

CASE

WHEN Ind.Fill_Factor = 0 THEN 100

ELSE Ind.Fill_Factor

END AS Fill_Factor,

Ind.Is_Padded AS IsPadded,

s.No_Recompute AS NoRecompute,

Ind.allow_row_locks AS AllowRowLocks,

Ind.allow_page_locks AS AllowPageLocks

FROM sys.indexes Ind

INNER JOIN sys.stats s

ON Ind.name = s.name

INNER JOIN sys.tables AS Tab

ON Tab.OBJECT_ID = Ind.OBJECT_ID

LEFT JOIN sys.filegroups FG

ON Ind.data_space_id = FG.data_space_id

WHERE tab.Name = ISNULL(@TableName, tab.Name)

AND Ind.Name IS NOT NULL

AND INDEXPROPERTY(Ind.OBJECT_ID, Ind.Name, 'IsStatistics') = 0

)

INSERT @udf_IndexColumns

(

DatabaseName,

TableSchema,

TableName,

IndexName,

IndexType,

IndexColumns,

IndexColumns_SO,

IncludedColumns,

IncludedColumns_SO,

StatisticsLastUpdated,

FileGroupName,

Fill_Factor,

IsPadded,

NoRecompute,

AllowRowLocks,

AllowPageLocks

)

-- list the clustered and nonclustered

SELECT

DB_NAME() AS DatabaseName,

cte1.TableSchema AS TableSchema,

cte1.TableName AS TableName,

cte1.IndexName AS IndexName,

cte1.IndexType AS IndexName,

cte1.IndexColumns AS IndexColumns,

cte1.IndexColumns_SO AS IndexColumns_SO,

cte1.IncludedColumns AS IncludedColumns,

cte1.IncludedColumns_SO AS IncludedColumns_SO,

cte1.StatisticsLastUpdated AS StatisticsLastUpdated,

cte1.FileGroupName AS FileGroupName,

cte1.Fill_Factor AS Fill_Factor,

cte1.IsPadded AS IsPadded ,

cte1.NoRecompute AS NoRecompute ,

cte1.AllowRowLocks AS AllowRowLocks ,

cte1.AllowPageLocks AS AllowPageLocks

FROM AllIndexes_CTE cte1

WHERE IndexType IN ('CLustered', 'NonClustered')

UNION

-- list the heaps, but only where there are no other indexes ie 0 indexes on table and only a heap entry

SELECT

DB_NAME() AS DatabaseName,

cte2.TableSchema AS TableSchema,

cte2.TableName AS TableName,

cte2.IndexName AS IndexName,

cte2.IndexType AS IndexName,

cte2.IndexColumns AS IndexColumns ,

cte2.IndexColumns_SO AS IndexColumns_SO ,

cte2.IncludedColumns AS IncludedColumns,

cte2.IncludedColumns_SO AS IncludedColumns_SO,

NULL AS StatisticsLastUpdated,

cte2.FileGroupName AS FileGroupName,

cte2.Fill_Factor AS Fill_Factor,

cte2.IsPadded AS IsPadded ,

cte2.NoRecompute AS NoRecompute ,

cte2.AllowRowLocks AS AllowRowLocks ,

cte2.AllowPageLocks AS AllowPageLocks

FROM AllIndexes_CTE cte2

WHERE IndexType IN ('Heap')

AND (SELECT COUNT(*) FROM AllIndexes_CTE cte3 WHERE cte3.TableName = cte2.TableName) = 1

ORDER BY cte1.TableName, cte1.IndexType, cte1.IndexName

RETURN

END

/*

USE AdventureWorksDW

GO

SELECT * FROM dbo.udf_GetIndexColumns(NULL)

SELECT * FROM dbo.udf_GetIndexColumns('DimCustomer')

SELECT * FROM dbo.udf_GetIndexColumns('Calendar')

*/

USE AdventureWorksDW

GO

SELECT * FROM dbo.udf_GetIndexColumns(NULL)

SELECT * FROM dbo.udf_GetIndexColumns('DimCustomer')