Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List of all index & index columns in SQL Server DB

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 appear in index definition) plus as much metadata as possible.

like image 382
Anton Gogolev Avatar asked Apr 19 '09 18:04

Anton Gogolev


People also ask

How many indexes are there?

Key Takeaways There are approximately 5,000 U.S. indexes.

How many indexes are there in the world?

There are over 3 million stock indices in the world, according to the Index Industry Association.

What are the four indexes?

Key Takeaways Common indexes include the Dow Jones Industrial Average, the S&P 500, the Nasdaq, and the Russell 3000. Each of these performs differently based on the composition of its stocks.


2 Answers

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; 
like image 166
marc_s Avatar answered Sep 21 '22 18:09

marc_s


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.

like image 21
Dragon Avatar answered Sep 21 '22 18:09

Dragon