Mega Code Archive

 

Categories / MSSQL Tutorial / Data Types

 

Retrieving Index Properties by Using the Bitwise AND (&) Operator

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)