Wednesday, May 4, 2011

SQL Server: How to Get All Indexes List With Involved Columns Name

Recently a friend of mine asked for a script, for documentation purpose which can help them to create all of their indexes list with column names used in each index. I thought, I must share this simple script with my blog readers. 



(Indexes list with usage statistics can be found here)

34 comments:

  1. Yeah, uh. This doesn't work.

    ReplyDelete
  2. Syntax error removed.. Now its working fine.

    ReplyDelete
  3. Made of win, this is a beautiful query to hook up to my index usage scripts, so I can immediately see which columns are involved in the unused/under-used indexes. Nice work!!

    ReplyDelete
  4. This is really one of those great scripts! Thank you so much!

    ReplyDelete
  5. thanks for the script

    there is a problem with it, however - it is putting the "included" columns into the same list as index columns

    to distinguish them is_included_column flag of
    sys.[index_columns] table should be used

    ReplyDelete
  6. Thanks for posting this great script! It is a big help and nice work adding the suggestions of the commenters.

    ReplyDelete
  7. Great script!! beautifuly done... thanks a lot.

    ReplyDelete
  8. Great, that's what I was looking for, Thank you a lot!

    ReplyDelete
  9. Great. It is very helpful. Thanks a lot.

    ReplyDelete
  10. Great script - would be even better if you can make it display (select) the Schema as well, before TableName

    ReplyDelete
  11. Big help!!! Marvelous, Best part of this query if list of include columns

    ReplyDelete
  12. Thank you nice script specially to get include cols list.

    ReplyDelete
  13. Thank you, just what I needed!

    ReplyDelete
  14. Cool, thanks a lot,
    I searched everywhere an tried to write my own script without success!

    Great script!

    ReplyDelete
  15. Many thanks.
    This worked like magic. Learnt a lot from it.

    ReplyDelete
  16. I get a lot of NULL values. I had to add WHERE Ind.[name] is not null

    ReplyDelete
  17. Excellent work! very nice script.

    ReplyDelete
  18. Excellent!! Thanks

    ReplyDelete
  19. the link to the script doesn't work. Does anyone has a valid link to it?
    thanks!

    ReplyDelete
    Replies
    1. You can directly download it from github https://gist.github.com/lionofdezert/3722342#file-alldbindexeslistwithincludecolumns-sql

      Delete
  20. Works perfectly thx :)

    ReplyDelete
  21. you saved my days.. thanks a lot for great work..really appreciate it!!

    ReplyDelete
  22. while executing this query I am getting error ORA-01747: invalid user.table.column, table.column, or column specification
    01747. 00000 - "invalid user.table.column, table.column, or column specification"
    please help me on this

    ReplyDelete
    Replies
    1. Sorry dear, but the above mention script works only for SQL Server.

      Delete
  23. Thanks, Aasim, for sharing knowledge! Could you please help me for a script in which we may analyze performance of the indexes created on specified table(s)?

    ReplyDelete
  24. Here is a method to include data for XML indexes
    SELECT DISTINCT * FROM (
    SELECT
    SI.name,
    STUFF((
    SELECT ';' + SC.name
    FROM sys.index_columns SIC
    INNER JOIN sys.columns SC
    ON SC.object_id = SIC.object_id
    AND SC.column_id = SIC.column_id
    WHERE SI.object_id = SIC.object_id
    AND SI.index_id = SIC.index_id
    AND SIC.is_included_column = 0
    ORDER BY SIC.index_column_id
    FOR XML PATH ('')),1,1,'') AS KeyColumns
    ,STUFF((
    SELECT ';' + SC.name
    FROM sys.index_columns SIC
    INNER JOIN sys.columns SC
    ON SC.object_id = SIC.object_id
    AND SC.column_id = SIC.column_id
    WHERE SI.object_id = SIC.object_id
    AND SI.index_id = SIC.index_id
    AND SIC.is_included_column = 1
    ORDER BY SIC.index_column_id
    FOR XML PATH ('')),1,1,'') AS IncludeColumns
    ,SI.index_id
    ,SXI.xml_index_type_description
    ,SXI.secondary_type
    ,sxi.secondary_type_desc
    ,sxi.using_xml_index_id
    FROM sys.indexes SI
    INNER JOIN sys.sysobjects SO
    ON SO.id = SI.object_id
    INNER JOIN sys.schemas SS
    ON SO.uid = SS.schema_id
    INNER JOIN sys.index_columns SIC
    ON SIC.object_id = SI.object_id
    AND SIC.index_id= SI.index_id
    INNER JOIN sys.columns SC
    ON SC.object_id = SI.object_id
    AND SC.column_id = SIC.column_id
    LEFT OUTER JOIN sys.xml_indexes SXI
    ON SI.object_id = SXI.object_id
    AND SI.index_id = SXI.index_id
    WHERE SO.name = 'Person'
    AND SS.name = 'Person'
    ) AS X
    ORDER BY X.index_id

    ReplyDelete

All suggestions are welcome