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