Post reply

Index with included columns

  • March 15, 2011 at 8:46 am

    #389053

    I have created some indexes with included columns. If at a later time I need to add a column to my query how can I get a list of indexes with included columns so I can see which index I need to drop and recreate

  • March 15, 2011 at 9:18 am

    #1298909

    sp_help [tablename] is the fastest; it identifies all indexes and their columns on the table in question.

    i've got a script that scripts out all columns and their includes as well, but it's more of a script all kind of thing, not tailored to a specific table; is that what you want?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 15, 2011 at 2:33 pm

    #1299120

    sys.index_columns view is your friend, it list regular and included columns for an index:

    SELECTOBJECT_NAME(sx.object_id) AS table_name

    ,si.name AS index_name

    ,sc.name AS column_name

    ,sx.is_included_column AS is_included

    ,sc.*

    FROMsys.index_columns sx

    INNER JOIN sysindexes si

    ONsi.id = sx.object_id

    ANDsi.indid = sx.index_id

    INNER JOIN syscolumns sc

    ONsc.id = sx.object_id

    ANDsc.colid = sx.column_id

    WHEREsx.object_id = OBJECT_ID('your table')

    ANDsi.name = 'your index'

  • March 19, 2011 at 12:02 am

    #1300893

    That was exactly what I was looking for

    Thank you

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

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