List all Indexes of All Tables in the Current Database

The indexing schema plays a fundamental role in performance tuning, and tools such as the Index Tuning Wizard that can suggest efficient schemas on the grounds of a database usage. Once the indexing schema has been completed, you should document it in order to know which tables have primary keys, which have clustered keys, which and how many are the non clustered indexes of a certain table. You can get this metadata through SQL Server Information Schema’s views, that are based on the following system tables: sysobjects, syscolumns, sysreferences, spt_values, and sysindexes.

Unfortunately these tables don’t provide all the necessary values, but the Books Online documentation lets to derive the meaning of all the fields in these system tables. The following sp_help_db_indexes stored procedure, placed inside the master database, lets to view all the indexes of each table for the current database, with the respective name, type and fields that make it up:

Author : Giuseppe Dimauro

declare @empty varchar(1)
select @empty = ''

-- 35 is the length of the name field of the master.dbo.spt_values table
declare    @IgnoreDuplicateKeys varchar(35),
@Unique varchar(35),
@IgnoreDuplicateRows varchar(35),
@Clustered varchar(35),
@Hypotethical varchar(35),
@Statistics varchar(35),
@PrimaryKey varchar(35),
@UniqueKey varchar(35),
@AutoCreate varchar(35),
@StatsNoRecompute varchar(35)

select    @IgnoreDuplicateKeys = name
from    master.dbo.spt_values
where    type = 'I' and number = 1 --ignore duplicate keys

select    @Unique = name
from    master.dbo.spt_values
where    type = 'I' and number = 2 --unique

select    @IgnoreDuplicateRows = name
from    master.dbo.spt_values
where    type = 'I' and number = 4 --ignore duplicate rows

select    @Clustered = name
from    master.dbo.spt_values
where    type = 'I' and number = 16 --clustered

select    @Hypotethical = name
from    master.dbo.spt_values
where    type = 'I' and number = 32 --hypotethical

select    @Statistics = name
from    master.dbo.spt_values
where    type = 'I' and number = 64 --statistics

select    @PrimaryKey = name
from    master.dbo.spt_values
where    type = 'I' and number = 2048 --primary key

select    @UniqueKey = name
from    master.dbo.spt_values
where    type = 'I' and number = 4096 --unique key

select    @AutoCreate = name
from    master.dbo.spt_values
where    type = 'I' and number = 8388608 --auto create

select    @StatsNoRecompute = name
from    master.dbo.spt_values
where    type = 'I' and number = 16777216 --stats no recompute

select    o.name,
i.name,
'index description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on
case
when (i.status & 16)<>0 then @Clustered
else 'non'+@Clustered
end +
case
when (i.status & 1) <> 0 then ', '+ @IgnoreDuplicateKeys
else @empty
end +
case
when (i.status & 2) <> 0 then ', ' + @Unique
else @empty
end +
case
when (i.status & 4) <> 0 then ', ' + @IgnoreDuplicateRows
else @empty
end +
case
when (i.status & 64) <> 0 then ', ' + @Statistics
else case
when (i.status & 32) <> 0 then ', ' + @Hypotethical
else @empty
end
end +
case
when (i.status & 2048) <> 0 then ', ' + @PrimaryKey
else @empty
end +
case
when (i.status & 4096) <> 0 then ', ' + @UniqueKey
else @empty
end +
case
when (i.status & 8388608) <> 0 then ', ' + @AutoCreate
else @empty
end +
case
when (i.status & 16777216) <> 0 then ', ' + @StatsNoRecompute
else @empty
end),
'index column 1' = index_col(o.name,indid, 1),
'index column 2' = index_col(o.name,indid, 2),
'index column 3' = index_col(o.name,indid, 3),
'index column 4' = index_col(o.name,indid, 4),
'index column 5' = index_col(o.name,indid, 5),
'index column 6' = index_col(o.name,indid, 6),
'index column 7' = index_col(o.name,indid, 7),
'index column 9' = index_col(o.name,indid, 9),
'index column 10' = index_col(o.name,indid, 10),
'index column 11' = index_col(o.name,indid, 11),
'index column 12' = index_col(o.name,indid, 12),
'index column 13' = index_col(o.name,indid, 13),
'index column 14' = index_col(o.name,indid, 14),
'index column 15' = index_col(o.name,indid, 15),
'index column 16' = index_col(o.name,indid, 16),
'index column 17' = index_col(o.name,indid, 17),
'index column 18' = index_col(o.name,indid, 18),
'index column 19' = index_col(o.name,indid, 19),
'index column 20' = index_col(o.name,indid, 20)
from    sysindexes i, sysobjects o
where    i.id = o.id
and        indid > 0
and        indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255)
and        o.type = 'U' --user table
--ignore the indexes for the autostat
and        (i.status & 64) = 0 --index with duplicates
and        (i.status & 8388608) = 0 --auto created index
and        (i.status & 16777216)= 0 --stats no recompute
order by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20