Post reply

sp_helpindex doesn''t tell about covering index !

  • September 11, 2007 at 4:36 am

    #180646

    Here is an example:

    create

    table testing (fid int, ftest varchar(max))

    create index idx_tmp on testing (fid) include (ftest)

    sp_helpindex

    testing

    output

    :

    index_name index_description index_keys

    idx_tmp nonclustered located on PRIMARY fid

    The question: Where can I see that ftest was also included in the index ?

     

    Thank

    R

     

  • September 11, 2007 at 4:51 am

    #732732

    Unfortunately sp_helpindex does not contain this information. You have to dig this out from the system views. The ones you will need are sys.indexes and sys.index_columns. The later has a is_included_column.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 11, 2007 at 4:54 am

    #732733

    A simple example:

    SELECT  sts.name
      , st.name
      , si.name
      , ssc.name
      , is_included_column
FROM    sys.tables AS st
        JOIN sys.schemas AS sts ON sts.schema_id = st.schema_id
        JOIN sys.indexes AS si ON st.object_id = si.object_id
        JOIN sys.index_columns AS sc ON si.object_id = sc.object_id
                                        AND si.index_id = sc.index_id
        JOIN sys.columns AS ssc ON si.object_id = ssc.object_id
                                   AND sc.column_id = ssc.column_id
ORDER BY st.object_id
      , si.index_id
      , sc.column_id

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 11, 2007 at 4:57 am

    #732734

    Got it , thank you.

     

    R

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply