-- list of indexs by status/type
select 
cast(object_name(id) + '.' + i.name as varchar(50)), min(v.name), i.status
from sysindexes i
inner join master.dbo.spt_values v
on i.status & v.number = v.number
where v.type = 'I' 
and i.id > 100
and i.name not like '_WA_Sys%'
and v.Name <> 'INDEX TYPES'
and i.status > 0
group by i.id, i.name, i.status
order by i.id, i.name, i.status


select cast(object_name(id) + '.' + name as varchar(50)) , status
from sysindexes
where id > 100
and name not like '_WA_Sys%'
--and status <> 
order by object_name(id)


sp_helpindex alert_reports

alert_reports.main                                 16402 clustered unique
alert_reports.cust_num                             0 nonclustered
alert_reports.symbol                               0 nonclustered
alert_reports.index_229575856                      2 nonclustered unique

select distinct i.status as status, v.name 
from sysindexes i
left join master..spt_values v
on i.status & v.number = v.number
where v.type = 'I' 
and i.status > 0
and i.id > 100
and i.name not like '_WA_Sys%'
and v.Name <> 'INDEX TYPES'
and v.number > 0
union
select 0 as status,'nonclustered' as name
order by status

select * from master..spt_values


0
8388704
4098
10485856
20498
2097154
2050
18450
16400
49171
3
18
2113552
2
16402
2097152
2113554
8