40> SELECT
41> object_name([id]) AS table_name,
42> [indid] AS index_id,
43> [name] as index_name,
44> status,
45> CASE
46> WHEN status & 2 = 2 THEN 'Yes'
47> ELSE 'No'
48> END AS is_unique,
49> CASE
50> WHEN status & 16 = 16 THEN 'Yes'
51> ELSE 'No'
52> END AS is_clustered,
53> CASE
54> WHEN status & 2048 = 2048 THEN 'Yes'
55> ELSE 'No'
56> END AS is_PK_CNS,
57> CASE
58> WHEN status & 4096 = 4096 THEN 'Yes'
59> ELSE 'No'
60> END AS is_UNQ_CNS
61> FROM
62> sysindexes
63> WHERE
64> indid BETWEEN 1 AND 254 -- clustered and nonclustered indexes
65> ORDER BY
66> table_name,
67> index_id
68> GO
table_name
index_id index_name
status is_unique is_clustered is_PK_CNS is_UNQ_CNS
------------------------------------------------------------------------------------------------------------------------
-------- -------- ------------------------------------------------------------------------------------------------------
-------------------------- ----------- --------- ------------ --------- ----------
MSreplication_options
2 _WA_Sys_00000001_4460231C
8388704 No No No No
queue_messages_1003150619
1 queue_clustered_index
18 Yes Yes No No
queue_messages_1003150619
2 queue_secondary_index
2 Yes No No No
queue_messages_1035150733
1 queue_clustered_index
18 Yes Yes No No
queue_messages_1035150733
2 queue_secondary_index
2 Yes No No No
queue_messages_1067150847
1 queue_clustered_index
18 Yes Yes No No
queue_messages_1067150847
2 queue_secondary_index
2 Yes No No No
spt_values
1 spt_valuesclust
18 Yes Yes No No
spt_values
2 ix2_spt_values_nu_nc
0 No No No No
spt_values
3 _WA_Sys_00000001_4277DAAA
8388704 No No No No
sysallocunits
1 clust
18 Yes Yes No No
sysasymkeys
1 cl
18 Yes Yes No No
sysasymkeys
2 nc1
2 Yes No No No
sysasymkeys
3 nc3
2 Yes No No No
sysbinobjs
1 clst
18 Yes Yes No No
sysbinobjs
2 nc1
2 Yes No No No
sysbinsubobjs
1 clst
18 Yes Yes No No
sysbinsubobjs
2 nc1
2 Yes No No No
syscerts
1 cl
18 Yes Yes No No
syscerts
2 nc1
2 Yes No No No
syscerts
3 nc2
2 Yes No No No
syscerts
4 nc3
2 Yes No No No
syschildinsts
1 cl
18 Yes Yes No No
sysclsobjs
1 clst
18 Yes Yes No No
sysclsobjs
2 nc
2 Yes No No No
sysclsobjs
11 _WA_Sys_00000002_00000040
8388704 No No No No
sysclsobjs
12 _WA_Sys_00000006_00000040
8388704 No No No No
syscolpars
1 clst
18 Yes Yes No No
syscolpars
2 nc
2 Yes No No No
syscolpars
11 _WA_Sys_00000002_00000029
8388704 No No No No
syscolpars
12 _WA_Sys_0000000B_00000029
8388704 No No No No
syscolpars
13 _WA_Sys_00000003_00000029
8388704 No No No No
syscolpars
14 _WA_Sys_0000000F_00000029
8388704 No No No No
syscolpars
15 _WA_Sys_0000000E_00000029
8388704 No No No No
syscolpars
16 _WA_Sys_00000006_00000029
8388704 No No No No
sysconvgroup
1 clst
18 Yes Yes No No
sysdbfiles
1 clst
18 Yes Yes No No
sysdbfiles
11 _WA_Sys_00000008_0000004C
8388704 No No No No
sysdbfiles
12 _WA_Sys_0000000C_0000004C
8388704 No No No No
sysdbfiles
13 _WA_Sys_0000000D_0000004C
8388704 No No No No
sysdbfiles
14 _WA_Sys_00000002_0000004C
8388704 No No No No
sysdbreg
1 clst
18 Yes Yes No No
sysdbreg
2 nc1
2 Yes No No No
sysdbreg
3 nc2
2 Yes No No No
sysdbreg
11 _WA_Sys_00000004_0000001C
8388704 No No No No
sysdbreg
12 _WA_Sys_00000005_0000001C
8388704 No No No No
sysdbreg
13 _WA_Sys_00000006_0000001C
8388704 No No No No
sysdercv
1 cl
18 Yes Yes No No
sysdesend
1 cl
18 Yes Yes No No
sysendpts
1 clst
18 Yes Yes No No
sysendpts
2 nc1
2 Yes No No No
sysftinds
1 clst
18 Yes Yes No No
sysftinds
11 _WA_Sys_00000003_00000019
8388704 No No No No
sysguidrefs
1 cl
18 Yes Yes No No
sysguidrefs
2 nc
2 Yes No No No
syshobtcolumns
1 clust
18 Yes Yes No No
syshobts
1 clust
18 Yes Yes No No
sysidxstats
1 clst
18 Yes Yes No No
sysidxstats
2 nc
2 Yes No No No
sysidxstats
11 _WA_Sys_00000004_00000036
8388704 No No No No
sysidxstats
12 _WA_Sys_00000002_00000036
8388704 No No No No
sysidxstats
13 _WA_Sys_00000009_00000036
8388704 No No No No
sysidxstats
14 _WA_Sys_00000008_00000036
8388704 No No No No
sysiscols
1 clst
18 Yes Yes No No
syslnklgns
1 cl
18 Yes Yes No No
syslnklgns
11 _WA_Sys_00000002_00000030
8388704 No No No No
syslnklgns
12 _WA_Sys_00000004_00000030
8388704 No No No No
syslogshippers
1 clst
18 Yes Yes No No
sysmultiobjrefs
1 clst
18 Yes Yes No No
sysmultiobjrefs
2 nc1
2 Yes No No No
sysmultiobjrefs
11 _WA_Sys_00000001_0000004B
8388704 No No No No
sysmultiobjrefs
12 _WA_Sys_00000003_0000004B
8388704 No No No No
sysmultiobjrefs
13 _WA_Sys_00000005_0000004B
8388704 No No No No
sysnsobjs
1 clst
18 Yes Yes No No
sysnsobjs
2 nc
2 Yes No No No
sysnsobjs
11 _WA_Sys_00000002_0000002C
8388704 No No No No
sysnsobjs
12 _WA_Sys_00000004_0000002C
8388704 No No No No
sysobjkeycrypts
1 cl
18 Yes Yes No No
sysobjkeycrypts
11 _WA_Sys_00000002_0000005E
8388704 No No No No
sysobjkeycrypts
12 _WA_Sys_00000003_0000005E
8388704 No No No No
sysobjkeycrypts
13 _WA_Sys_00000004_0000005E
8388704 No No No No
sysobjvalues
1 clst
18 Yes Yes No No
sysobjvalues
11 _WA_Sys_00000002_0000003C
8388704 No No No No
sysobjvalues
12 _WA_Sys_00000003_0000003C
8388704 No No No No
sysobjvalues
13 _WA_Sys_00000004_0000003C
8388704 No No No No
sysobjvalues
14 _WA_Sys_00000005_0000003C
8388704 No No No No
sysowners
1 clst
18 Yes Yes No No
sysowners
2 nc1
2 Yes No No No
sysowners
3 nc2
2 Yes No No No
sysowners
11 _WA_Sys_00000003_0000001B
8388704 No No No No
sysprivs
1 clust
18 Yes Yes No No
sysprivs
11 _WA_Sys_00000002_0000001D
8388704 No No No No
sysprivs
12 _WA_Sys_00000004_0000001D
8388704 No No No No
sysprivs
13 _WA_Sys_00000005_0000001D
8388704 No No No No
sysprivs
14 _WA_Sys_00000006_0000001D
8388704 No No No No
sysqnames
1 clst
18 Yes Yes No No
sysqnames
2 nc1
2 Yes No No No
sysremsvcbinds
1 clst
18 Yes Yes No No
sysremsvcbinds
2 nc1
2 Yes No No No
sysremsvcbinds
3 nc2
2 Yes No No No
sysrmtlgns
1 cl
18 Yes Yes No No
sysrmtlgns
11 _WA_Sys_00000003_0000002F
8388704 No No No No
sysrowsetcolumns
1 clust
18 Yes Yes No No
sysrowsetrefs
1 clust
18 Yes Yes No No
sysrowsets
1 clust
18 Yes Yes No No
sysrts
1 clst
18 Yes Yes No No
sysrts
2 nc1
2 Yes No No No
sysrts
3 nc2
2 Yes No No No
sysscalartypes
1 clst
18 Yes Yes No No
sysscalartypes
2 nc1
2 Yes No No No
sysscalartypes
3 nc2
2 Yes No No No
sysschobjs
1 clst
18 Yes Yes No No
sysschobjs
2 nc1
2 Yes No No No
sysschobjs
3 nc2
2 Yes No No No
sysschobjs
4 nc3
0 No No No No
sysschobjs
11 _WA_Sys_00000006_00000022
8388704 No No No No
sysschobjs
12 _WA_Sys_00000008_00000022
8388704 No No No No
sysschobjs
13 _WA_Sys_00000003_00000022
8388704 No No No No
sysserefs
1 clust
18 Yes Yes No No
syssingleobjrefs
1 clst
18 Yes Yes No No
syssingleobjrefs
2 nc1
2 Yes No No No
syssingleobjrefs
11 _WA_Sys_00000001_0000004A
8388704 No No No No
syssingleobjrefs
12 _WA_Sys_00000003_0000004A
8388704 No No No No
syssqlguides
1 clst
18 Yes Yes No No
syssqlguides
2 nc1
2 Yes No No No
syssqlguides
3 nc2
2 Yes No No No
systypedsubobjs
1 clst
18 Yes Yes No No
systypedsubobjs
2 nc
2 Yes No No No
sysusermsgs
1 clst
18 Yes Yes No No
sysusermsgs
11 _WA_Sys_00000002_0000002D
8388704 No No No No
sysusermsgs
12 _WA_Sys_00000003_0000002D
8388704 No No No No
syswebmethods
1 clst
18 Yes Yes No No
sysxlgns
1 cl
18 Yes Yes No No
sysxlgns
2 nc1
2 Yes No No No
sysxlgns
3 nc2
2 Yes No No No
sysxlgns
11 _WA_Sys_00000005_0000002A
8388704 No No No No
sysxmitqueue
1 clst
18 Yes Yes No No
sysxmlcomponent
1 cl
18 Yes Yes No No
sysxmlcomponent
2 nc1
2 Yes No No No
sysxmlfacet
1 cl
18 Yes Yes No No
sysxmlplacement
1 cl
18 Yes Yes No No
sysxmlplacement
2 nc1
2 Yes No No No
sysxprops
1 clust
18 Yes Yes No No
sysxsrvs
1 cl
18 Yes Yes No No
sysxsrvs
2 nc1
2 Yes No No No
sysxsrvs
11 _WA_Sys_00000005_0000002B
8388704 No No No No
(146 rows affected)