Post reply

Script to find composite indexes in sql server 2005

  • May 4, 2009 at 12:57 am

    #133769

    Hi,

    Need to find the Script to identify composite indexes in sql server 2005

    With Regards

    Dakshina Murthy

  • May 4, 2009 at 3:36 am

    #987988

    Explain a bit more please?

    Do you just want to know what indexes have more than one column in them, or do you want to list the columns as well?

    Either way, the view that you'll need for this are sys.indexes and sys.index_columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 6, 2009 at 9:23 am

    #989651

    Hope this helps

    SELECT

    DISTINCT so.name AS [Table Name] ,sch.name AS [Schema Name], si.name AS [Index Name],si.Type_desc AS [Index Type] ,

    CASE

    WHEN si.index_id = 1 THEN 'clustered' ELSE 'nonclustered' END

    +

    CASE WHEN ignore_dup_key 0 THEN ', ignore duplicate keys' ELSE '' END

    +

    CASE WHEN is_unique 0 THEN ', unique' ELSE '' END

    +

    CASE WHEN is_hypothetical 0 THEN ', hypothetical' ELSE '' END

    +

    CASE WHEN is_primary_key 0 THEN ', primary key' ELSE '' END AS [Index Description],

    sc.name AS [Column Name]

    FROM sys.objects so

    JOIN

    sys.indexes si ON so.[object_id]=si.[object_id]

    JOIN

    sys.index_columns sic on sic.index_id=si.index_id

    JOIN

    sys.columns sc ON so.[object_id] = sc.[object_id]

    and sic.[object_id]=so.[object_id]

    JOIN

    sys.Schemas sch ON sch.[schema_id]=so.[schema_id]

    WHERE

    so.type='U' and si.index_id0

    ORDER

    BY so.name

    A script I use from Phani Tata but slightly modified.

    Rgds

    JL

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

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