SQL Server: Listing all indexes

Question

I'm wondering what the simplest way to list all indexes for all tables in a database is. Should I call sp_helpindex for each table and store the results in a temp table, or is there an easier way? Can anyone explain why constraints are stored in sysobjects but indexes are not?

Answer 1

You could reference sysindexes

Another trick is to look at the text of sp_helpindex to see how it reconstructs information from the underlying tables.

sp_helptext 'sp_helpindex'

I don't have a reference for this, but I believe constraints are not stored in sysobjects because they are a different kind of thing; sysindexes contains meta-data about objects in sysobjects.

Answer 2

If you need more information, here is a nice SQL script, which I use from time to time:

DECLARE @TabName varchar(100)

CREATE TABLE #temp (
   TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), 
   IndexKeys varchar(200), IndexSize int
)

DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
    SELECT name FROM sysobjects WHERE xtype = 'U'

OPEN cur

FETCH NEXT FROM cur INTO @TabName
WHILE @@FETCH_STATUS = 0
    BEGIN
     INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
     EXEC sp_helpindex @TabName

     UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL

     FETCH NEXT FROM cur INTO @TabName
    END

CLOSE cur
DEALLOCATE cur

DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'

UPDATE #temp SET IndexSize = 
    ((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
     FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
      INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name

SELECT * FROM #temp
ORDER BY TabName, IndexName 

DROP TABLE #temp

Answer 3

Here's an example of the kind of query you need:

select 
    i.name as IndexName, 
    o.name as TableName, 
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    co.[name] as ColumnName
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id 
    and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id 
    and co.column_id = ic.column_id
where i.[type] = 2 
and i.is_unique = 0 
and i.is_primary_key = 0
and o.[type] = 'U'
--and ic.is_included_column = 0
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
;

This one is somewhat specific to a certain purpose (I use it in a little C# app to find duplicate indexes and format the output so it's actually readable by a human). But you could easily adapt it to your needs.

Answer 4

In addition to all the answers so far - which works just fine, but are SQL Server specific, using sysobjects or sys.* catalog views - you could also consult the ISO standard INFORMATION_SCHEMA views.

These are supported across multiple database vendors, e.g. SQL Server, MySQL, PostgreSQL and more. The one you're most likely looking for is the

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

which will list out unique, primary key and foreign key constraints - everything that gets turned into an index on SQL Server.

Marc