Cool Tips‎ > ‎Indexes‎ > ‎

Indexes - Get Index Columns

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown/ChillyDBA
Date:        7 Nov 2010/7 Jul 2012
                 20 Jun 2014 - ChillyDBA - Bracketed the returned index key columns to allow for spaces in column names

Description

Returns a list of indexes for one or all tables in the database (depending on input parameter).
All index key columns are returned.
If a table has no indexes, then one row with index type HEAP is returned.

I converted to a table-valued function and augmented the columns returned to included attributes that are consumed by the SP that produces the CREATE/DROP Index TSQL.

Code

Function:

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

*/



Test Code:


USE AdventureWorksDW
GO

SELECT * FROM dbo.udf_GetIndexColumns(NULL)

SELECT * FROM dbo.udf_GetIndexColumns('DimCustomer')


ċ
udf_GetIndexColumns.sql
(8k)
Andy Hughes,
Jun 23, 2014, 4:39 PM
Comments