Cacher is the code snippet organizer for pro developers

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.
					

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

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.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,
			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')

*/