List of all index index columns in SQL Server DB

Question

How do I get a list of all index  amp  index columns in SQL Server 2005   The closest I could get is   select s name  t name  i name  c name from sys tables t inner join sys schemas s on t schema id   s schema id inner join sys indexes i on i object id   t object id inner join sys index columns ic on ic object id   t object id inner join sys columns c on c object id   t object id and         ic column id   c column id  where i index id  gt  0      and i type in  1  2  -- clustered  amp  nonclustered only  and i is primary key   0 -- do not include PK indexes  and i is unique constraint   0 -- do not include UQ  and i is disabled   0  and i is hypothetical   0  and ic key ordinal  gt  0  order by ic key ordinal   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  amp  primary keys  with all columns  ordered by how do they appear in index definition  plus as much metadata as possible

User · Answer

May I hazard another answer to this saturated question?

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.

SELECT 
    OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema],
    t.[name] AS [TableName], 
    ind.[name] AS [IndexName], 
    col.[name] AS [ColumnName],
    ic.column_id AS [ColumnId],
    ind.[type_desc] AS [IndexTypeDesc], 
    col.is_identity AS [IsIdentity],
    ind.[is_unique] AS [IsUnique],
    ind.[is_primary_key] AS [IsPrimaryKey],
    ic.[is_descending_key] AS [IsDescendingKey],
    ic.[is_included_column] AS [IsIncludedColumn]
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 
    t.is_ms_shipped = 0
    --ind.is_primary_key = 1 -- include or not pks, etc
    --AND ind.is_unique = 0
    --AND ind.is_unique_constraint = 0 
ORDER BY 
    [Schema],
    TableName, 
    IndexName,
    [ColumnId],
    ColumnName

User · Answer

For unique columns per index   select s name  t name  i name  i index id c name c column id  from sys schemas s inner join sys tables t on t schema id   s schema id inner join sys indexes i on i object id   t object id inner join sys index columns ic on ic object id   t object id     and ic index id i index id inner join sys columns c on c object id   t object id      and ic column id   c column id where i object id   object id  previous account 1     order by index id column id

User · Answer

Following gives what is similar as sp helpindex tablename  select T name as TableName  I name as IndexName  AC Name as ColumnName  I type desc as IndexType  from sys tables as T inner join sys indexes as I on T  object id    I  object id      inner join sys index columns as IC on IC  object id    I  object id  and IC  index id    I  index id      inner join sys all columns as AC on IC  object id    AC  object id  and IC  column id    AC  column id   order by T name  I name

User · Answer

Since your profile states that you are using  NET you could use Server Managed Objects  SMO  programmatically    otherwise any of the above answers are fantastic

User · Answer

In Oracle  select CONNECYBY SCHEMA NAME CONNECYBY TABLE NAME CONNECYBY INDEX NAME CONNECYBY COLUMN NAME from    select TABLE OWNER SCHEMA NAME TABLE NAME INDEX NAME COLUMN POSITION trim     from sys connect by path COLUMN NAME       COLUMN NAME         from DBA IND COLUMNS         start with COLUMN POSITION   1         connect by TABLE OWNER   prior TABLE OWNER         and TABLE NAME   prior TABLE NAME         and INDEX NAME   prior INDEX NAME         and COLUMN POSITION   prior COLUMN POSITION   1  CONNECYBY join    select TABLE OWNER SCHEMA NAME TABLE NAME INDEX NAME max COLUMN POSITION  COLUMN POSITION         from DBA IND COLUMNS         group by TABLE OWNER TABLE NAME INDEX NAME  MAX CONNECYBY on      CONNECYBY SCHEMA NAME   MAX CONNECYBY SCHEMA NAME         and CONNECYBY TABLE NAME   MAX CONNECYBY TABLE NAME         and CONNECYBY INDEX NAME   MAX CONNECYBY INDEX NAME         and CONNECYBY COLUMN POSITION   MAX CONNECYBY COLUMN POSITION  order by CONNECYBY SCHEMA NAME CONNECYBY TABLE NAME CONNECYBY INDEX NAME   In SQL Server with   CONNECTBY SCHEMA NAME TABLE NAME INDEX NAME INDEX COLUMN ID COLUMN NAME   as          select SCHEMAS NAME SCHEMA NAME               TABLES NAME TABLE NAME               INDEXES NAME INDEX NAME               INDEX COLUMNS INDEX COLUMN ID INDEX COLUMN ID               cast COLUMNS NAME AS VARCHAR MAX   COLUMN NAME         from SYS INDEXES         join SYS TABLES on  INDEXES OBJECT ID   TABLES OBJECT ID          join SYS SCHEMAS on  TABLES SCHEMA ID   SCHEMAS SCHEMA ID          join SYS INDEX COLUMNS on   INDEXES OBJECT ID   INDEX COLUMNS OBJECT ID                                      and INDEX COLUMNS INDEX ID   INDEXES INDEX ID          join SYS COLUMNS on     INDEXES OBJECT ID   COLUMNS OBJECT ID                                  and INDEX COLUMNS COLUMN ID   COLUMNS COLUMN ID          where INDEX COLUMNS INDEX COLUMN ID   1         union all         select SCHEMAS NAME SCHEMA NAME               TABLES NAME TABLE NAME               INDEXES NAME INDEX NAME               INDEX COLUMNS INDEX COLUMN ID INDEX COLUMN ID               cast PRIOR COLUMN NAME         COLUMNS NAME AS VARCHAR MAX   COLUMN NAME         from SYS INDEXES         join SYS TABLES on  INDEXES OBJECT ID   TABLES OBJECT ID          join SYS SCHEMAS on  TABLES SCHEMA ID   SCHEMAS SCHEMA ID          join SYS INDEX COLUMNS on   INDEXES OBJECT ID   INDEX COLUMNS OBJECT ID                                      and INDEX COLUMNS INDEX ID   INDEXES INDEX ID          join SYS COLUMNS on     INDEXES OBJECT ID   COLUMNS OBJECT ID                                  and INDEX COLUMNS COLUMN ID   COLUMNS COLUMN ID          join CONNECTBY as PRIOR on  SCHEMAS NAME   PRIOR SCHEMA NAME                                      and TABLES NAME   PRIOR TABLE NAME                                      and INDEXES NAME   PRIOR INDEX NAME                                      and INDEX COLUMNS INDEX COLUMN ID   PRIOR INDEX COLUMN ID   1   select CONNECTBY SCHEMA NAME CONNECTBY TABLE NAME CONNECTBY INDEX NAME CONNECTBY COLUMN NAME from CONNECTBY join    select  SCHEMA NAME                   TABLE NAME                   INDEX NAME                   MAX INDEX COLUMN ID  INDEX COLUMN ID         from CONNECTBY          group by SCHEMA NAME TABLE NAME INDEX NAME  MAX CONNECTBY         on  CONNECTBY SCHEMA NAME   MAX CONNECTBY SCHEMA NAME             and CONNECTBY TABLE NAME   MAX CONNECTBY TABLE NAME             and CONNECTBY INDEX NAME   MAX CONNECTBY INDEX NAME             and CONNECTBY INDEX COLUMN ID   MAX CONNECTBY INDEX COLUMN ID  order by CONNECTBY SCHEMA NAME CONNECTBY TABLE NAME CONNECTBY INDEX NAME

User · Answer

I have needed to get particular indexes  their index columns and their included columns as well  Here is the query I have used   SELECT INX  name  AS  Index Name         TBL  name  AS  Table Name         DS1  IndexColumnsNames         DS2  IncludedColumnsNames  FROM  sys   indexes  INX INNER JOIN  sys   tables  TBL     ON INX  object id    TBL  object id  CROSS APPLY        SELECT STUFF                             SELECT        CLS  name                    FROM  sys   index columns  INXCLS             INNER JOIN  sys   columns  CLS                  ON INXCLS  object id    CLS  object id                   AND INXCLS  column id    CLS  column id              WHERE INX  object id    INXCLS  object id                   AND INX  index id    INXCLS  index id                  AND INXCLS  is included column    0             FOR XML PATH                        1          1                      DS1   IndexColumnsNames   CROSS APPLY        SELECT STUFF                             SELECT        CLS  name                    FROM  sys   index columns  INXCLS             INNER JOIN  sys   columns  CLS                  ON INXCLS  object id    CLS  object id                   AND INXCLS  column id    CLS  column id              WHERE INX  object id    INXCLS  object id                   AND INX  index id    INXCLS  index id                  AND INXCLS  is included column    1             FOR XML PATH                        1          1                      DS2   IncludedColumnsNames

User · Answer

I came up with this one  which is giving me the exact overview I need  What is helps is that you get one row per index into which the index columns are aggregated    select      o name as ObjectName       i name as IndexName       i is primary key as  PrimaryKey       SUBSTRING i  type desc  0 6  as IndexType      i is unique as  Unique       Columns  Normal  as IndexColumns      Columns  Included  as IncludedColumns from sys indexes i  join sys objects o on i object id   o object id cross apply       select         substring                                         select        co  name                  from sys index columns ic                 join sys columns co on co object id   i object id and co column id   ic column id                 where ic object id   i object id and ic index id   i index id and ic is included column   0                 order by ic key ordinal                 for xml path                                 3               10000              as  Normal                substring                                         select        co  name                  from sys index columns ic                 join sys columns co on co object id   i object id and co column id   ic column id                 where ic object id   i object id and ic index id   i index id and ic is included column   1                 order by ic key ordinal                 for xml path                                 3               10000              as  Included         Columns where o  type     U  --USER TABLE order by o  name   i  name   i is primary key desc

User · Answer

based on Tim Ford code  this is the right answer     select tab  name   as  table name            idx  name   as  index name            allc  name  as  column name            idx  type desc            idx  is unique            idx  data space id            idx  ignore dup key            idx  is primary key            idx  is unique constraint            idx  fill factor            idx  is padded            idx  is disabled            idx  is hypothetical            idx  allow row locks            idx  allow page locks            idxc  is descending key            idxc  is included column            idxc  index column id        from sys  tables  as tab      inner join sys  indexes        idx  on tab  object id     idx  object id      inner join sys  index columns  idxc on idx  object id    idxc  object id  and  idx  index id     idxc  index id      inner join sys  all columns    allc on tab  object id    allc  object id  and idxc  column id    allc  column id       where tab  name  Like   table name         and idx  name  Like   index name       order by tab  name   idx  index id   idxc  index column id

User · Answer

I gave KFD9 s answer an update    I adapted their version to support the include-specification and not make use of indexkey property which is deprecated  This gives you a create and a drop statement for indexes and constraints   with indexes as       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      as  Type    -- B basic  C Clustered  X XML        select string agg CAST       c name         case when is descending key   1 then   DESC  else    end AS NVARCHAR MAX         within group  order by index column id            from sys index columns ic JOIN sys columns c on ic column id   c column id and ic object id   c object id where ic index id   si index id and ic object id   si object id and ic is included column   0  Cols         select string agg CAST       c name         case when is descending key   1 then   DESC  else    end AS NVARCHAR MAX         within group  order by index column id            from sys index columns ic JOIN sys columns c on ic column id   c column id and ic object id   c object id where ic index id   si index id and ic object id   si object id and ic is included column   1  IncludedCols         select count    from sys index columns ic where ic index id   si index id and ic object id   si object id  IndexColsCount     FROM sys indexes as si     LEFT JOIN sys objects as so on so object id si object id     WHERE index id gt 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 sysdiagrams   SELECT 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          Cols        isnull   include    IncludedCols                as CreateIndex      CASE pk     WHEN  PK  THEN  ALTER          TABLE    SchemaName       TableName    DROP CONSTRAINT    IndexName          ELSE  DROP INDEX    IndexName    ON    SchemaName       TableName           END AS DropIndex      IndexColsCount FROM indexes ORDER BY SchemaName TableName IndexName

User · Answer

Here is the best way to do it   SELECT sys tables object id  sys tables name as table name  sys columns name as column name  sys indexes name as index name  sys indexes is unique  sys indexes is primary key  FROM sys tables  sys indexes  sys index columns  sys columns  WHERE  sys tables object id   sys indexes object id AND sys tables object id   sys index columns object id AND sys tables object id   sys columns object id AND sys indexes index id   sys index columns index id AND sys index columns column id   sys columns column id   AND sys tables name    your table name   I prefer using implicit joins as it s much easier for me to understand  You can remove the object id reference as you might not need it   Cheers

User · Answer

There are two  quot sys quot  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

User · Answer

The following works on SQL Server 2014 2016 as well as any Microsoft Azure SQL Database   Produces a comprehensive result set that is easily exportable to Notepad Excel for slicing and dicing and includes        Table Name   Index Name   Index Description   Indexed Columns - In order   Included Columns - In order       SELECT       s NAME           o NAME       AS  table name         i NAME AS  index name       LOWER i type desc    CASE          WHEN i is unique   1             THEN    unique          ELSE            END   CASE          WHEN i is primary key   1             THEN    primary key          ELSE            END AS  index description       STUFF               SELECT         sc NAME       AS  text                FROM syscolumns AS sc             INNER JOIN sys index columns AS ic ON ic object id   sc id                 AND ic column id   sc colid             WHERE sc id   so object id                 AND ic index id   i1 indid                 AND ic is included column   0             ORDER BY key ordinal             FOR XML PATH                    1  2      AS  indexed columns       STUFF               SELECT         sc NAME       AS  text                FROM syscolumns AS sc             INNER JOIN sys index columns AS ic ON ic object id   sc id                 AND ic column id   sc colid             WHERE sc id   so object id                 AND ic index id   i1 indid                 AND ic is included column   1             FOR XML PATH                    1  2      AS  included columns  FROM sysindexes AS i1 INNER JOIN sys indexes AS i ON i object id   i1 id     AND i index id   i1 indid INNER JOIN sysobjects AS o ON o id   i1 id INNER JOIN sys objects AS so ON so object id   o id     AND is ms shipped   0 INNER JOIN sys schemas AS s ON s schema id   so schema id WHERE so type    U      AND i1 indid  lt  255     AND i1 STATUS  amp  64   0 --index with duplicates     AND i1 STATUS  amp  8388608   0 --auto created index     AND i1 STATUS  amp  16777216   0 --stats no recompute     AND i type desc  lt  gt   heap      AND so NAME  lt  gt   sysdiagrams  ORDER BY table name      index name

User · Answer

The correct One is here  All the above posts will give Cartesian product result when we have more then one index on a table  select s name  t name  i name  c name from sys tables t inner join sys schemas s on t schema id   s schema id inner join sys indexes i on i object id   t object id inner join sys index columns ic on ic object id   t object id                                    AND i index id   ic index id inner join sys columns c on c object id   t object id                                    and  ic column id   c column id where i index id  gt  0      and i type in  1  2  -- clustered  amp  nonclustered only  and i is primary key   0 -- do not include PK indexes  and i is unique constraint   0 -- do not include UQ  and i is disabled   0  and i is hypothetical   0  and ic key ordinal  gt  0  AND  t name    DimCustomer  order by ic key ordinal

User · Answer

Based on the accepted answer and two other questions 1  2 I have assembled the following query   SELECT     QUOTENAME t name  AS TableName      QUOTENAME i name  AS IndexName      i is primary key      i is unique      i is unique constraint      STUFF REPLACE REPLACE           SELECT QUOTENAME c name    CASE WHEN ic is descending key   1 THEN   DESC  ELSE    END AS  data            FROM sys index columns AS ic         INNER JOIN sys columns AS c ON ic object id   c object id AND ic column id   c column id         WHERE ic object id   i object id AND ic index id   i index id AND ic is included column   0         ORDER BY ic key ordinal         FOR XML PATH          lt row gt             lt  row gt         1  2      AS KeyColumns      STUFF REPLACE REPLACE           SELECT QUOTENAME c name  AS  data            FROM sys index columns AS ic         INNER JOIN sys columns AS c ON ic object id   c object id AND ic column id   c column id         WHERE ic object id   i object id AND ic index id   i index id AND ic is included column   1         ORDER BY ic index column id         FOR XML PATH          lt row gt             lt  row gt         1  2      AS IncludedColumns      u user seeks      u user scans      u user lookups      u user updates FROM sys tables AS t INNER JOIN sys indexes AS i ON t object id   i object id LEFT JOIN sys dm db index usage stats AS u ON i object id   u object id AND i index id   u index id WHERE t is ms shipped   0 AND i type  lt  gt  0   This query returns results such as below which shows the list of indexes  their columns and usage  Very helpful in determining which index is performing better than others

User · Answer

Working solution for SQL Server 2014  I have included only a handful of output fields here but feel free to add in as many as you like   SELECT     o object id AS objectId      o name AS objectName      i index id AS indexId      i name AS indexName      i type desc AS typeDesc      ic index column id AS indexColumnId      ic key ordinal AS keyOrdinal      ic is included column AS isIncludedColumn      ic column id AS columnId      c name AS columnName FROM  database  sys objects AS o     INNER JOIN  database  sys columns AS c ON         c object id   o object id         AND o type    U      INNER JOIN  database  sys indexes AS i ON         i object id   o object id     INNER JOIN  database  sys index columns AS ic ON         ic object id   i object id         AND ic index id   i index id         AND ic column id   c column id ORDER BY     o object id      i index id      ic index column id

User · Answer

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   SELECT   s  name  AS  schema name    t  name  AS  table name    i  name  AS  index name    dbo Concatenate CASE WHEN ic  key ordinal   gt  0 AND ic  is descending key    1 THEN c  name      DESC  WHEN key ordinal  gt  0 THEN c  name  ELSE NULL END     1  AS  columns    dbo Concatenate CASE WHEN ic  is included column    1 THEN c  name  ELSE NULL END     1  AS  includes  FROM   sys tables t INNER JOIN   sys schemas s ON t  schema id    s  schema id  INNER JOIN   sys indexes i ON i  object id    t  object id  INNER JOIN   sys index columns ic ON ic  object id    t  object id  AND ic index id   i index id INNER JOIN   sys columns c ON c  object id    t  object id  AND ic column id   c column id GROUP BY   s  name    t  name    i  name  ORDER BY   s  name    t  name    i  name    There are lots of concatenation aggregates out there if your environment allows CLR-based functions added to it

User · Answer

I have used the following query when I had this requirement     SELECT      TableName   t name      ColumnId   col column id       ColumnName   col name      DataType   ty name      MaxSize   ty max length      IsNullable   CASE WHEN  col is nullable   1  THEN  Y  END      IsIdentity   CASE WHEN  col is identity   1  THEN  Y  END      IsPrimaryKey   CASE WHEN  ic column id   col column id  THEN  Y  END      IsForeignKey   CASE WHEN  fkc parent column id   col column id  THEN  Y  END      IsDefault   CASE WHEN  dc parent column id   col column id  THEN  Y  END FROM      sys tables t INNER JOIN       sys columns col ON t object id   col object id  LEFT JOIN     sys indexes ind ON t object id   ind object id  LEFT JOIN       sys index columns ic ON ic index id ind index id AND ic object id   col object id and ic column id   col column id LEFT JOIN sys foreign key columns fkc                 ON fkc parent object id   col object id AND fkc parent column id col column id LEFT JOIN sys default constraints dc                 ON dc parent object id   col object id AND dc parent column id col column id LEFT JOIN      sys types ty on ty user type id   col user type id  WHERE     --t name   lt TABLENAME gt       t schema id   10    --SCHEMA ID     AND ind is primary key 1     ORDER BY     t name  ColumnId

User · Answer

sELECT       TableName   t name       IndexName   ind name       --IndexId   ind index id       ColumnId   ic index column id       ColumnName   col name       key ordinal       ind type desc      --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       and t name  CompanyReconciliation  --table name      and key ordinal gt 0 ORDER BY       t name  ind name  ind index id  ic index column id

User · Answer

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

User · Answer

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 Key6 FROM sys indexes as si LEFT JOIN sys objects as so on so object id si object id WHERE index id gt 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 sysdiagrams ORDER BY SchemaName TableName IndexName  ------------------------------------------------------------------- -- or to generate creation scripts put a simple wrapper around that SELECT 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 CreateIndex FROM              listing SQL same as above minus the ORDER BY              as indexes ORDER BY SchemaName TableName IndexName  ---------------------------------------------------------- -- For SQL Server 2000 the following should work --   change table names to sysindexes and sysobjects  no dots  --   change object id   gt  id  index id   gt  indid  --   change is primary key   gt   select count constid  from sysconstraints as sc where sc id si id and sc status amp 15 1  --   change is unique   gt  INDEXPROPERTY si id si name  IsUnique   --   change si type   gt  INDEXPROPERTY si id si name  IsClustered   --   remove all references to schemas including schema name qualifiers  and the XML type --   add select where indid lt 255 and si status amp 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

User · Answer

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   AM

User · Answer

Using SQL Server 2016  this gives a complete list of all indexes  with an included dump of each table so you can see how the tables relate  It also shows columns included in covering indexes   select t name TableName  i name IdxName  c name ColName       ic index column id ColPosition       i type desc Type       case when i is primary key   1 then  Yes  else    end  Primary         case when i is unique   1 then  Yes  else    end  Unique         case when ic is included column   0 then    else  Yes - Included  end  CoveredColumn          indexes  gt  gt  gt  gt     indexes    i     index columns  gt  gt  gt  gt     index columns         ic     tables  gt  gt  gt  gt     tables         t     columns  gt  gt  gt  gt     columns    c   from sys index columns ic join sys tables t on t object id   ic object id join sys columns c on c object id   t object id and c column id   ic column id join sys indexes i on i object id   t object id and i index id   ic index id order by TableName  IdxName  ColPosition

User · Answer

--Short and sweet   SELECT OBJECT SCHEMA NAME T  object id  DB ID    AS  Schema       T  name  AS  table name   I  name  AS  index name   AC  name  AS  column name       I  type desc   I  is unique   I  data space id   I  ignore dup key   I  is primary key      I  is unique constraint   I  fill factor      I  is padded   I  is disabled   I  is hypothetical      I  allow row locks   I  allow page locks   IC  is descending key   IC  is included column   FROM sys  tables  AS T     INNER JOIN sys  indexes  I ON T  object id    I  object id      INNER JOIN sys  index columns  IC ON I  object id    IC  object id     INNER JOIN sys  all columns  AC ON T  object id    AC  object id  AND IC  column id    AC  column id   WHERE T  is ms shipped    0 AND I  type desc   lt  gt   HEAP   ORDER BY T  name   I  index id   IC  key ordinal

User · Answer

I didn t go through  but I got what I wanted in the query posted by the original author  I used it  without conditions filters  for my requirement but it gave incorrect results The main problem was the results getting cross product without join condition on index id SELECT S NAME SCHEMA NAME T NAME TABLE NAME I NAME INDEX NAME C NAME COLUMN NAME   FROM SYS TABLES T        INNER JOIN SYS SCHEMAS S     ON T SCHEMA ID   S SCHEMA ID        INNER JOIN SYS INDEXES I     ON I OBJECT ID   T OBJECT ID        INNER JOIN SYS INDEX COLUMNS IC     ON IC OBJECT ID   T OBJECT ID        INNER JOIN SYS COLUMNS C     ON C OBJECT ID    T OBJECT ID      AND IC INDEX ID      I INDEX ID      AND IC COLUMN ID   C COLUMN ID  WHERE 1 1  ORDER BY I NAME I INDEX ID IC KEY ORDINAL

User · Answer

this will work    DECLARE  IndexInfo  TABLE  index name         varchar 250                             index description  varchar 250                             index keys         varchar 250                               INSERT INTO  IndexInfo exec sp msforeachtable  sp helpindex        select   from  IndexInfo   this does not reurn the table name and you will get warnings for all tables without an index  if that is a problem  you can create a loop over the tables that have indexes like this   DECLARE  IndexInfoTemp  TABLE  index name         varchar 250                                 index description  varchar 250                                 index keys         varchar 250                                   DECLARE  IndexInfo  TABLE  table name         sysname                            index name         varchar 250                             index description  varchar 250                             index keys         varchar 250                               DECLARE  Tables Table  RowID       int not null identity 1 1                         TableName   sysname                          DECLARE  MaxRow       int DECLARE  CurrentRow   int DECLARE  CurrentTable sysname  INSERT INTO  Tables     SELECT         DISTINCT t name          FROM sys indexes i             INNER JOIN sys tables t ON i object id   t object id         WHERE i Name IS NOT NULL SELECT  MaxRow   ROWCOUNT  CurrentRow 1  WHILE  CurrentRow lt   MaxRow BEGIN      SELECT  CurrentTable TableName FROM  Tables WHERE RowID  CurrentRow      INSERT INTO  IndexInfoTemp     exec sp helpindex  CurrentTable      INSERT INTO  IndexInfo              table name     index name   index description   index keys          SELECT              CurrentTable   index name   index description   index keys         FROM  IndexInfoTemp      DELETE FROM  IndexInfoTemp      SET  CurrentRow  CurrentRow 1  END --WHILE SELECT   from  IndexInfo   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

User · Answer

The query below includes all of the pertinent information for the user-defined indexes   no indexes for unique constraints  amp  primary keys  with all columns   SELECT I name as IndexName           CASE WHEN I is unique   1 THEN  Yes  ELSE  No  END as  Unique           I type desc COLLATE DATABASE DEFAULT as Index Type                SCHEMA NAME T schema id        as  Schema                 T name       as TableName          STUFF  SELECT         C name   CASE WHEN IC is descending key   0 THEN    ASC  ELSE    DESC  END             FROM sys index columns IC INNER JOIN sys columns C ON  IC object id   C object id  AND IC column id   C column id             WHERE IC is included column   0 AND IC object id   I object id AND IC index id   I Index id             FOR XML PATH       1  2      as Key Columns          Included Columns           I filter definition          CASE WHEN I is padded   1 THEN  ON  ELSE  OFF  END as PAD INDEX           CASE WHEN ST no recompute   0 THEN  OFF  ELSE  ON  END as  Statistics Norecompute           CONVERT VARCHAR 5   CASE WHEN I fill factor   0 THEN 100 ELSE I fill factor END  as  Fillfactor           CASE WHEN I ignore dup key   1 THEN  ON  ELSE  OFF  END as  Ignore Dup Key                  CASE WHEN I allow row locks   1 THEN  ON  ELSE  OFF  END as  Allow Row Locks            CASE WHEN I allow page locks   1 THEN  ON  ELSE  OFF  END  Allow Page Locks          FROM    sys indexes I INNER JOIN                 sys tables T ON  T object id   I object id INNER JOIN                sys stats ST ON  ST object id   I object id AND ST stats id   I index id INNER JOIN          sys data spaces DS ON  I data space id   DS data space id INNER JOIN          sys filegroups FG ON  I data space id   FG data space id LEFT OUTER JOIN           SELECT   FROM               SELECT IC2 object id  IC2 index id                  STUFF  SELECT        C name FROM sys index columns IC1 INNER JOIN                      sys columns C ON C object id   IC1 object id                         AND C column id   IC1 column id                         AND IC1 is included column   1                     WHERE  IC1 object id   IC2 object id AND IC1 index id   IC2 index id                     GROUP BY IC1 object id  C name  index id  FOR XML PATH                        1  2      as Included Columns             FROM sys index columns IC2             GROUP BY IC2 object id  IC2 index id  tmp1             WHERE Included Columns IS NOT NULL           tmp2         ON tmp2 object id   I object id AND tmp2 index id   I index id WHERE I is primary key   0 AND I is unique constraint   0    As an added bonus  the below query is formatted to write out the create index and drop index scripts   SELECT I name as IndexName           -- Uncommnent line below to include checking for index exists as part of the script         -- IF NOT EXISTS  SELECT name FROM sysindexes WHERE name        I name                    CREATE     CASE WHEN I is unique   1 THEN   UNIQUE   ELSE    END           I type desc COLLATE DATABASE DEFAULT     INDEX              I name      ON      SCHEMA NAME T schema id            T name           STUFF           SELECT         C name   CASE WHEN IC is descending key   0 THEN    ASC  ELSE    DESC  END             FROM sys index columns IC INNER JOIN sys columns C ON  IC object id   C object id  AND IC column id   C column id             WHERE IC is included column   0 AND IC object id   I object id AND IC index id   I Index id             FOR XML PATH       1  2                        ISNULL   INCLUDE      IncludedColumns                       ISNULL   WHERE     I filter definition                  WITH  PAD INDEX       CASE WHEN I is padded   1 THEN  ON  ELSE  OFF  END               STATISTICS NORECOMPUTE       CASE WHEN ST no recompute   0 THEN  OFF  ELSE  ON  END               SORT IN TEMPDB   OFF                FILLFACTOR       CONVERT VARCHAR 5   CASE WHEN I fill factor   0 THEN 100 ELSE I fill factor END               IGNORE DUP KEY       CASE WHEN I ignore dup key   1 THEN  ON  ELSE  OFF  END                    ONLINE   OFF                ALLOW ROW LOCKS       CASE WHEN I allow row locks   1 THEN  ON  ELSE  OFF  END               ALLOW PAGE LOCKS       CASE WHEN I allow page locks   1 THEN  ON  ELSE  OFF  END               ON      DS name          CHAR 13    CHAR 10     GO  as  CreateIndex            DROP INDEX     I name     ON     SCHEMA NAME T schema id          T name                 CHAR 13    CHAR 10     GO  AS  DropIndex  FROM    sys indexes I INNER JOIN                 sys tables T ON  T object id   I object id INNER JOIN                sys stats ST ON  ST object id   I object id AND ST stats id   I index id INNER JOIN          sys data spaces DS ON  I data space id   DS data space id INNER JOIN          sys filegroups FG ON  I data space id   FG data space id LEFT OUTER JOIN           SELECT   FROM               SELECT IC2 object id  IC2 index id                  STUFF  SELECT        C name FROM sys index columns IC1 INNER JOIN                      sys columns C ON C object id   IC1 object id                         AND C column id   IC1 column id                         AND IC1 is included column   1                     WHERE  IC1 object id   IC2 object id AND IC1 index id   IC2 index id                     GROUP BY IC1 object id  C name  index id  FOR XML PATH                        1  2      as IncludedColumns             FROM sys index columns IC2             GROUP BY IC2 object id  IC2 index id  tmp1             WHERE IncludedColumns IS NOT NULL           tmp2         ON tmp2 object id   I object id AND tmp2 index id   I index id WHERE I is primary key   0 AND I is unique constraint   0

User · Answer

with connect schema name table name index name index column id column name  as     select s name schema name  t name table name  i name index name  index column id  cast c name as varchar max   column name  from sys tables t inner join sys schemas s on t schema id   s schema id inner join sys indexes i on i object id   t object id inner join sys index columns ic on ic object id   t object id and ic index id i index id         inner join sys columns c on c object id   t object id and                 ic column id   c column id                 where index column id 1 union all select s name schema name  t name table name  i name index name  ic index column id  cast connect column name         c name as varchar max   column name  from sys tables t inner join sys schemas s on t schema id   s schema id inner join sys indexes i on i object id   t object id inner join sys index columns ic on ic object id   t object id and ic index id i index id         inner join sys columns c on c object id   t object id and                 ic column id   c column id join connect on connect index column id 1   ic index column id and connect schema name   s name and connect table name   t name and connect index name   i name  select connect schema name connect table name connect index name connect column name from connect join  select schema name table name index name MAX index column id  index column id from connect group by schema name table name index name  mx on connect schema name   mx schema name and connect table name   mx table name and connect index name   mx index name and connect index column id   mx index column id order by 1 2 3

User · Answer

The above solution is elegant  but according to MS  INDEXKEY PROPERTY is being deprecated  See  http   msdn microsoft com en-us library ms186773 aspx

User · Answer

This is mine  works on one default schema but it can be easily improved It gives 3 columnns with SQLQueries - Create   Drop   Rebuild  no reorganizing   Query   SELECT  CREATE      CASE WHEN is primary key 1 THEN  CLUSTERED   WHEN is primary key 0 and is unique constraint 0 THEN  NONCLUSTERED  WHEN is primary key 0 and is unique constraint 1 THEN  UNIQUE  END       INDEX     QUOTENAME i name      ON     QUOTENAME t name              STUFF REPLACE REPLACE           SELECT QUOTENAME c name    CASE WHEN ic is descending key   1 THEN   DESC  ELSE    END AS  data            FROM sys index columns AS ic         INNER JOIN sys columns AS c ON ic object id   c object id AND ic column id   c column id         WHERE ic object id   i object id AND ic index id   i index id AND ic is included column   0         ORDER BY ic key ordinal         FOR XML PATH          lt row gt             lt  row gt         1  2               -- keycols   COALESCE   INCLUDE           STUFF REPLACE REPLACE           SELECT QUOTENAME c name  AS  data            FROM sys index columns AS ic         INNER JOIN sys columns AS c ON ic object id   c object id AND ic column id   c column id         WHERE ic object id   i object id AND ic index id   i index id AND ic is included column   1         ORDER BY ic index column id         FOR XML PATH          lt row gt             lt  row gt         1  2                  -- included cols         as  Create    DROP INDEX     QUOTENAME i name      ON     QUOTENAME t name  as  Drop    ALTER INDEX     QUOTENAME i name       ON    QUOTENAME t name      REBUILD   as  Rebuild  FROM sys tables AS t INNER JOIN sys indexes AS i ON t object id   i object id LEFT JOIN sys dm db index usage stats AS u ON i object id   u object id AND i index id   u index id WHERE t is ms shipped   0 AND i type  lt  gt  0 order by QUOTENAME t name   is primary key desc   Output  Create                                                                                                      Drop                                    Rebuild ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE CLUSTERED INDEX  PK Table1  ON  Table1     Tab1 ID                                                   DROP INDEX  PK Table1  ON  Table1       ALTER INDEX  PK Table1  ON  Table1  REBUILD  CREATE UNIQUE INDEX  IX Table1 Name  ON  Table1     Tab1 Name                                               DROP INDEX  IX Table1 Name  ON  Table1  ALTER INDEX  IX Table1 Name  ON  Table1  REBUILD  CREATE NONCLUSTERED INDEX  IX Table2  ON  Table2     Tab2 Name    Tab2 City     INCLUDE    Tab2 PhoneNo     DROP INDEX  IX Table2  ON  Table2       ALTER INDEX  IX Table2  ON  Table2  REBUILD

User · Answer

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   --Show fragmentation info on all indexes in a database  SET NOCOUNT ON USE pubs DBCC SHOWCONTIG WITH ALL INDEXES GO      turn NOCOUNT back OFF when done  --Show fragmentation info on all indexes on a table  SET NOCOUNT ON USE pubs DBCC SHOWCONTIG  authors  WITH ALL INDEXES GO   --Show fragmentation information on a specific index  SET NOCOUNT ON USE pubs DBCC SHOWCONTIG  authors aunmind  GO

[sql-server] List of all index & index columns in SQL Server DB

Examples related to sql-server

Examples related to tsql

Examples related to indexing

Examples related to reverse-engineering