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)
at 8:42 PM
Labels: , , ,

34 comments:

  1. AnonymousMay 25, 2011 at 3:41 PM

    Yeah, uh. This doesn't work.

    ReplyDelete
  2. aasim abdullahMay 25, 2011 at 7:02 PM

    Syntax error removed.. Now its working fine.

    ReplyDelete
  3. CarsonJuly 4, 2011 at 11:20 PM

    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. AnonymousAugust 11, 2011 at 2:15 PM

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

    ReplyDelete
  5. AnonymousNovember 23, 2011 at 5:16 PM

    brilliant!

    ReplyDelete
  6. Madina SaitakhmetovaNovember 24, 2011 at 5:10 PM

    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
  7. aasim abdullahNovember 24, 2011 at 7:50 PM

    thanks, aedna.... Its corrected ;)

    ReplyDelete
  8. vinayDecember 16, 2011 at 5:24 AM

    it helped me thanks

    ReplyDelete
  9. AnonymousFebruary 8, 2012 at 12:14 AM

    Excellent

    ReplyDelete
  10. BarryMarch 22, 2012 at 9:08 PM

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

    ReplyDelete
  11. AnonymousApril 11, 2012 at 2:39 PM

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

    ReplyDelete
  12. AnonymousApril 12, 2012 at 9:49 PM

    Nice script

    ReplyDelete
  13. AnonymousMay 11, 2012 at 5:34 PM

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

    ReplyDelete
  14. AnonymousJuly 6, 2012 at 7:47 PM

    Great. It is very helpful. Thanks a lot.

    ReplyDelete
  15. Mr.SQLJuly 10, 2012 at 4:18 PM

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

    ReplyDelete
  16. AnonymousJuly 17, 2012 at 8:49 AM

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

    ReplyDelete
  17. AnonymousJuly 17, 2012 at 10:35 PM

    Thank you nice script specially to get include cols list.

    ReplyDelete
  18. AnonymousAugust 2, 2012 at 10:37 AM

    Thank you, just what I needed!

    ReplyDelete
  19. AnonymousAugust 28, 2012 at 10:03 AM

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

    Great script!

    ReplyDelete
  20. AnonymousOctober 9, 2012 at 9:35 PM

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

    ReplyDelete
  21. RoricOctober 26, 2012 at 4:36 PM

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

    ReplyDelete
  22. AnonymousMay 3, 2013 at 1:33 AM

    Excellent work! very nice script.

    ReplyDelete
  23. AnonymousMay 17, 2013 at 8:30 AM

    Excellent!! Thanks

    ReplyDelete
  24. AnonymousMay 28, 2013 at 3:19 PM

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

    ReplyDelete
    Replies
    1. aasim abdullahMay 28, 2013 at 6:21 PM

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

      Delete
  25. AnonymousAugust 12, 2013 at 6:27 PM

    Works perfectly thx :)

    ReplyDelete
  26. AnonymousAugust 29, 2013 at 8:01 AM

    thx

    ReplyDelete
  27. UnknownSeptember 30, 2013 at 8:20 PM

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

    ReplyDelete
  28. Arvind AJanuary 2, 2014 at 11:04 AM

    Thanks, it helped me

    ReplyDelete
  29. AnonymousMay 1, 2014 at 3:49 AM

    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. aasim abdullahMay 6, 2014 at 2:20 AM

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

      Delete
  30. FKNovember 4, 2015 at 1:35 PM

    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
  31. AnonymousMay 16, 2016 at 7:46 PM

    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

Subscribe to: Post Comments (Atom)