List of all index & index columns in SQL Server DB

There are two "sys" catalog views you can consult: sys.indexes and sys.index_columns.

Those will give you just about any info you could possibly want about indices and their columns.

EDIT: This query's getting pretty close to what you're looking for:

SELECT      TableName = t.name,     IndexName = ind.name,     IndexId = ind.index_id,     ColumnId = ic.index_column_id,     ColumnName = col.name,     ind.*,     ic.*,     col.* FROM      sys.indexes ind INNER JOIN      sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN      sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN      sys.tables t ON ind.object_id = t.object_id WHERE      ind.is_primary_key = 0      AND ind.is_unique = 0      AND ind.is_unique_constraint = 0      AND t.is_ms_shipped = 0 ORDER BY      t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

You can use the sp_helpindex to view all the indexes of one table.

EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)

And for all the indexes, you can traverse sys.objects to get all the indexes for each table.

None of the above did the job for me, but this does:

-- KDF9's concise index list for SQL Server 2005+  (see below for 2000)--   includes schemas and primary keys, in easy to read format--   with unique, clustered, and all ascending/descendings in a single column-- Needs simple manual add or delete to change maximum number of key columns--   but is easy to understand and modify, with no UDFs or complex logic--SELECT  schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,  (CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK,  (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+  (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  '' as 'Type',  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1,  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2,  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3,  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4,  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5,  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6FROM sys.indexes as siLEFT JOIN sys.objects as so on so.object_id=si.object_idWHERE index_id>0 -- omit the default heap  and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables  and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagramsORDER BY SchemaName,TableName,IndexName--------------------------------------------------------------------- or to generate creation scripts put a simple wrapper around thatSELECT SchemaName, TableName, IndexName,  (CASE pk    WHEN 'PK' THEN 'ALTER '+     'TABLE '+SchemaName+'.'+TableName+' ADD CONSTRAINT '+IndexName+' PRIMARY KEY'+     (CASE substring(Type,3,1) WHEN 'C' THEN ' CLUSTERED' ELSE '' END)    ELSE 'CREATE '+     (CASE substring(Type,1,1) WHEN '1' THEN 'UNIQUE ' ELSE '' END)+     (CASE substring(Type,3,1) WHEN 'C' THEN 'CLUSTERED ' ELSE '' END)+     'INDEX '+IndexName+' ON '+SchemaName+'.'+TableName    END)+  ' ('+    (CASE WHEN Key1 is null THEN '' ELSE      Key1+(CASE substring(Type,4+1,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+    (CASE WHEN Key2 is null THEN '' ELSE ', '+Key2+(CASE substring(Type,4+2,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+    (CASE WHEN Key3 is null THEN '' ELSE ', '+Key3+(CASE substring(Type,4+3,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+    (CASE WHEN Key4 is null THEN '' ELSE ', '+Key4+(CASE substring(Type,4+4,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+    (CASE WHEN Key5 is null THEN '' ELSE ', '+Key5+(CASE substring(Type,4+5,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+    (CASE WHEN Key6 is null THEN '' ELSE ', '+Key6+(CASE substring(Type,4+6,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+    ')' as CreateIndexFROM (  ...  ...listing SQL same as above minus the ORDER BY...  ...  ) as indexesORDER BY SchemaName,TableName,IndexName------------------------------------------------------------ For SQL Server 2000 the following should work--   change table names to sysindexes and sysobjects (no dots)--   change object_id => id, index_id => indid,--   change is_primary_key => (select count(constid) from sysconstraints as sc where sc.id=si.id and sc.status&15=1)--   change is_unique => INDEXPROPERTY(si.id,si.name,'IsUnique')--   change si.type => INDEXPROPERTY(si.id,si.name,'IsClustered')--   remove all references to schemas including schema name qualifiers, and the XML type--   add select where indid<255 and si.status&64=0 (to omit the text/image index and autostats)

If your names include spaces, add square brackets around them in the creation scripts.

When the last Key column is all nulls, you know that none are missing.

Filtering out primary keys etc as in the original request is trivial.

NOTE: Take care with this solution as it doesn't distinguish indexed and included columns.