It is obvious that the status and status2 columns of sysdatabases are being interpreted bitwise. Using &16, &32 and &256 serve as masks, to check whether the specific bit that we are interested in, is set or not.
You cannot check directly with the value, cos there might be other bits of the field set and this will result in different overall values.
Let me know if you follow that are need more information in checking bits of a field and I can explain in more detail.
Not entirely sure what each of these bits represent. (Not really an admin.)
...but here's what I found in sybooks (12.5)
(Focus on the specific masks that are in your query and see the explanation for that particular bit)
For status column
Table 54-3: status control bits in the sysdatabases table
select into/bulkcopy; can be set by user
trunc log on chkpt; can be set by user
no chkpt on recovery; can be set by user
Database created with for load option, or crashed while loading database, instructs recovery not to proceed
Database suspect; not recovered; cannot be opened or used; can be dropped only with dbcc dbrepair
ddl in tran; can be set by user
read only; can be set by user
dbo use only; can be set by user
single user; can be set by user
allow nulls by default; can be set by user
For status2 column
abort tran on log full; can be set by user
no free space acctg; can be set by user
auto identity; can be set by user
identity in nonunique index; can be set by user
Database is offline
Database is offline until recovery completes
Database is being recovered (internal use)
Database has suspect pages
Database is in the process of being upgraded
Database brought online for standby access
Database has some portion of the log which is not on a log-only device
I would appreciate if you could explain more on bit checking. I still dont understand the difference between (status2 = 16) and (status2 &16 = 16). I think, if other bits are set, then (status2 = 16) would be false ... and this would the same case with (status2 &16 = 16).
Am I missing something here?
Now the mask applied is 16 which in binary is 00010000 -- say B
Now (A == B) is FALSE, quite clearly
However, ((A & B) == B) is TRUE because..
using the bitwise AND
A & B = 16 (which is what the value of the mask B is)
Remember A & B is a bitwise AND operation.
so quite literally this may be represented as below
00010001 -- A
& 00010000 -- B
00010000 -- (result is nothing but B, the mask)
Here are the boolean rules for AND
0 & 0 = 0
0 & 1 = 0
1 & 0 = 0
1 & 1 = 1
These rules must applied to each matching bit of A and B.
In case you are curious, another way to check bitwise if a specific bit is set or not is simply to use the bitwise AND with a mask, and if the bit is set the result will be non-zero. If not set the result will always be ZERO
So in our example we can as well check if ((A & B) != 0)