which is not exactly what I want. What I want is to list all user-defined indexes (which means no indexes which support unique constraints & primary keys) with all columns (ordered by how do they apper in index definition) plus as much metadata as possible.

Yep, I'm aware of these, but I cannot arrange all the required "sys." catalogs so that they will produce meaningful output.
–
Anton GogolevApr 19 '09 at 19:06

1

New version is much better, but "and ind.is_unique = 0" is unneccessary: it filters out almost all required data. However, this query still includes too much system data, which I don't know how to get rid of.
–
Anton GogolevApr 19 '09 at 19:46

2

@My-Name-Is: the OP wanted to get all user-defined indexes (is_ms_shipped=0), but no primary keys (is_primary_key=0) and no indexes that are created to support unique constraints only (is_unique_constraint=0).
–
marc_sJun 3 '13 at 18:45

1

Brilliant. Thank you, from this I can discover not only the clustered primary keys, as other solutions allowed (including the order of the column), but also if one of those columns is DESC not ASC! See in the output 'is_descending_key'
–
Nicholas PetersenApr 30 '14 at 19:54

EDIT
if you want, you can filter the data, here are some examples (these work for either method):

SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%primary key%'
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%nonclustered%' AND index_description LIKE '%clustered%'
SELECT * FROM @IndexInfo WHERE index_description LIKE '%unique%'

Unfortunately it doesn't list the include columns as part of the index definition.
–
Craig NicholsonSep 16 '11 at 23:38

+1 for that. It is worth noting that SQL Server 2000 throws an "EXECUTE cannot be used as a source when inserting into a table variable." error for your code. Using a temporary table instead of a table variable resolves this easily.
–
TomalakDec 1 '11 at 16:36

This is a way of backing into the indexes. You can use SHOWCONTIG to assess fragmentation. It will list all of the indexes for the database or table, along with statistics. I would caution that on a large database, it can be long-running. For me, one of the benefits of this approach is that you don't have to be an admin to use it.

Just note that if you are going to use any of the above working queries to script your indexes, you need to incorporate filter_definition column from sys.indexes table in your queries to get the filter definition of non-clustered indexes in SQL 2008+

This is a liberal reworking of @marc_s answer, mixed with some stuff from @Tim Ford, with the goal of having a bit of a cleaner and simpler result set and final display and ordering for my current need.

First, please note that all the above queries may miss out or erroneously incorporate the INCLUDE columns of the indices. Also missing in some is the proper ordering and/or ASC/DESC option of the columns.

Modified the above query by jona. As an aside, in many of the database I use, I install my own CLR CONCATENATE aggregate function, so the code below depends on something like this being present. The above SQL statements reduce to a much more maintainable: