What Fields Are In My Clustered Index

Last week I had to investigate a problem with a third party databases in the Health industry and was told by the product owner to look into the potential issue and provide a set of recommendations. One of my objectives were to identify tables with clustered indexes and the composition of the index.

i.e if the clustered index had multiple columns and the if the fist column was not incremental

As usual,  I hope this script helps 

IF OBJECT_ID('TEMPDB..#Temp_Constraint_Columns') IS NOT NULL

      DROP TABLE TEMPDB..#Temp_Constraint_Columns

CREATE TABLE #Temp_Constraint_Columns

      (

            IID int identity(1,1)

            ,ObejctName varchar(100)

            ,Index_Type varchar(50)

            ,PK_YN      varchar(100)

            ,Object_Type varchar(10)

            ,KeyColumns varchar(500)

      )

     

INSERT INTO #Temp_Constraint_Columns 

      (

            ObejctName

            ,Index_Type

            ,PK_YN     

            ,Object_Type     

      )

SELECT OBJECT_NAME(SI.Object_ID) ObejctName ,

            SI.Type_Desc Index_Type  ,

            CASE

                  WHEN index_id = 1 and is_primary_key = 1 THEN 'CLUSTERED_(PK-YES)'

                  WHEN index_id = 1 and is_primary_key = 0 THEN 'CLUSTERED_(PK-NO)'

                  WHEN is_primary_key = 0 THEN 'HEAP-(PK-NO)'

            END PK_YN,

            SO.TYPE Object_Type

FROM sys.indexes SI

JOIN sys.objects SO ON SI.Object_ID = SO.Object_ID

WHERE SI.index_id in (0 ,1) AND – this identifies if the index is clustered or a heap

      SO.TYPE NOT IN ('S' ,'TF')

n  This section concatinates the columns in the index

SET NOCOUNT ON

DECLARE

      @CLUSTERED_Count int ,

      @LoopCnt int ,

      @ID int ,

      @TBName varchar(100),

      @ColumnName  varchar(500)

SET @LoopCnt = 1

SET @ID = 0

SET @TBName = ''

SET @ColumnName  = ''

SELECT @CLUSTERED_Count = count(1) FROM #Temp_Constraint_Columns WHERE Index_Type = 'CLUSTERED'

-- SELECT @CLUSTERED_Count

--SET  @CLUSTERED_Count = 10

--SELECT * FROM #Temp_Constraint_Columns WHERE Index_Type = 'CLUSTERED'

WHILE @CLUSTERED_Count >= @LoopCnt

BEGIN

      SELECT  TOP 1 --IID , ObejctName 

                  @ID = IID ,

                  @TBName = ObejctName 

      FROM #Temp_Constraint_Columns WHERE Index_Type = 'CLUSTERED'  AND IID >@ID

     

                 

      -- THIS SECTION WILL IDENTIFY THE COLUMNS IN THE INDEX   

      SELECT  

                  @ColumnName   = @ColumnName   + A.COLUMN_NAME

                 

      FROM

      (                

            SELECT      TOP 100 PERCENT

                        ' ' + INC.COLUMN_NAME

                        + ' [' + INC.DATA_TYPE             

                        + ' ('+ ISNULL(CONVERT(VARCHAR(10),INC.CHARACTER_MAXIMUM_LENGTH),'') + ')]   ||'      COLUMN_NAME

            FROM    sys.indexes AS i

            INNER JOIN

                        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID

                                                            AND i.index_id = ic.index_id

            JOIN sys.objects SO ON I.Object_ID = SO.Object_ID 

            JOIN INFORMATION_SCHEMA.COLUMNS INC ON

                  OBJECT_NAME(ic.OBJECT_ID) = INC.TABLE_NAME AND

                  COL_NAME(ic.OBJECT_ID,ic.column_id) =     INC.COLUMN_NAME                               

            WHERE   --i.is_primary_key = 1 and

                        SO.TYPE NOT IN ('S' ,'TF') AND 

                        I.Index_ID = 1

                        and  OBJECT_NAME(ic.OBJECT_ID) = @TBName

            ORDER BY IC.key_ordinal -- PLEASE DONT REMOVE THE ORDER BY CLAUSE      

      )  A

     

           

      UPDATE #Temp_Constraint_Columns

      SET KeyColumns = SUBSTRING(@ColumnName,2,LEN(@ColumnName))

      WHERE IID = @ID

                       

     

     

       --SELECT @LoopCnt  , @ID ,  @TBName , SUBSTRING(@ColumnName,2,LEN(@ColumnName))

      SET @LoopCnt = @LoopCnt +1

      SET @ColumnName = ''

     

END

SELECT * FROM #Temp_Constraint_Columns --WHERE PK_YN = 'CLUSTERED_PK-YES'