A query to list indexes in a database
I'm always hunting around my hard-drive for a query that lists indexes in a database, so here's one for everyone's (including my) future reference (SQL2K only)
select so.name as 'tablename'
, si.indid
, si.name as indname
, sik.keyno as colorder
, sc.name as colname
, case when si.status & 2048 <> 0 then 'y' else 'n' end as 'IsPrimaryKey'
, case when (si.status & 2 <> 0) or (si.status & 4096 <> 0) then 'y' else 'n' end as 'IsUnique'
, case when objectproperty(so.id, 'IsMSShipped') = 1 then 'y' else 'n' end as 'IsMSShipped'
from sysindexes si join sysobjects so on si.id = so.id
join sysindexkeys sik on si.id = sik.id and si.indid = sik.indid
join syscolumns sc on sik.id = sc.id and sik.colid = sc.colid
where si.status & 64 = 0-- and so.name = '" & tbl & "'
and objectproperty(so.id, 'IsMSShipped') = 0
order by so.name, si.indid, sik.keyno