List of all index & index columns in SQL Server DB

Question

How do I get a list of all index & 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 > 0    
and i.type in (1, 2) -- clustered & 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 > 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 & primary keys) with all columns (ordered by how do they apper in index definition) plus as much metadata as possible.

Answer 1

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

Answer 2

There are two "sys" catalog views you can consult:

select * from sys.indexes

select * from sys.index_columns

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

EDIT: okay, how about this query?? It's getting pretty close to what you're looking for, right??

select
    ind.name, ind.index_id, ic.index_column_id, 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.index_column_id

Marc

Answer 3

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<=@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%'

Answer 4

--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] <> 'HEAP' 
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]

Answer 5

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.

Answer 6

Here is another way. http://www.onedotnetway.com/sql-server-2008-get-all-indexes-in-a-database/

Answer 7

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.

Answer 8

Hey guys, 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