List index information for the current database

--****************************************************************************************
-- List index information for the current database
--****************************************************************************************
-- Version: 1.0
-- Author:Theo Ekelmans 
-- Email:theo@ekelmans.com
-- Date:2005-10-07
--****************************************************************************************

set nocount on 

select o.name as 'TableName',
i.name as 'IndexName',
CASE WHEN (i.status & 0x800)     = 0 THEN 0 ELSE 1 END AS 'Primary', 
CASE WHEN (i.status & 0x10)      = 0 THEN 0 ELSE 1 END AS 'Clustered', 
CASE WHEN (i.status & 0x2)       = 0 THEN 0 ELSE 1 END AS 'Unique', 
CASE WHEN (i.status & 0x1)       = 0 THEN 0 ELSE 1 END AS 'IgnoreDupKey', 
CASE WHEN (i.status & 0x4)       = 0 THEN 0 ELSE 1 END AS 'IgnoreDupRow', 
CASE WHEN (i.status & 0x1000000) = 0 THEN 0 ELSE 1 END AS 'NoRecompute', 
i.OrigFillFactor AS 'FillFactor', 
i.rowcnt as 'Est.RowCount',
i.reserved * cast(8 as bigint) as ReservedKB,  
i.used * cast(8 as bigint) as UsedKB,  
k.keyno as 'KeyNumber',
c.name as 'ColumnName',
t.name as 'DataType', 
c.xprec as 'Precision',
c.xscale as 'Scale', 
c.iscomputed as 'IsComputed', 
c.isnullable as 'IsNullable', 
c.collation as 'Collation'
from            sysobjects   o with(nolock)
inner join sysindexes   i with(nolock) on o.id    =  i.id
inner join sysindexkeys k with(nolock) on i.id    =  k.id    and    i.indid =  k.indid
inner join syscolumns   c with(nolock) on k.id    =  c.id    and    k.colid =  c.colid 
inner join systypes     t with(nolock) on c.xtype =  t.xtype 

where o.xtype <> 'S' -- Ignore system objects
and i.name not like '_wa_sys_%' -- Ignore statistics

order by
o.name, 
k.indid,
k.keyno