I know this thread is WAY out of date but some of us still work on SQL Server 2000 (not for much longer - new system is currently in build phase thank goodness) but you can check for any value from the status list as shown previously. This one...

1 = autoclose; set with sp_dboption.

4 = select into/bulkcopy; set with sp_dboption.

8 = trunc. log on chkpt; set with sp_dboption.

16 = torn page detection, set with sp_dboption.

32 = loading.

64 = pre recovery.

128 = recovering.

256 = not recovered.

512 = offline; set with sp_dboption.

1024 = read only; set with sp_dboption.

2048 = dbo use only; set with sp_dboption.

4096 = single user; set with sp_dboption.

32768 = emergency mode.

4194304 = autoshrink.

1073741824 = cleanly shutdown.

by using the BITWISE operator.

So, say you are listing databases from the sysdatabases table but you don't want to include databases that are offline or a single user mode you can put a where clause that states;

WHERE

(status & 512) = 0

AND

(status & 4096) = 0

Best demonstrated with some smaller statuses. Assume a database has status 2060 but you don't know if it has torn page detection (status = 16)

You SELECT CASE WHEN (status & 16) = 0 THEN 'NO Torn Page Detection' ELSE 'Torn Page Detection' END

Your bitwise operation is actually this... (2060 & 16)

In the background the two integer values are converted to binary (apologies for the poor formatting!)

decimal

value 16 2060

1 0 0

2 0 0

4 0 1

8 0 1

16 1 0

32 0 0

64 0 0

128 0 0

256 0 0

512 0 0

1024 0 0

2048 0 1

4096 0 0

And then each pair are multiplied together such as

0000000010000

*

0100000001100

basically when you are multiplying two binary numbers together 0*0 = 0, 1*0 = 0, 0*1 = 0, and 1*1 = 1

So we end up with 0000000000000 (no binary column has a 1 in both numbers).

The resulting value of zero means that the status of this database does NOThave Torn Page Detection.

if the status on the other hand had been 2076, then the two binary numbers multiplied would be

0000000010000

*

0100000011100

and the resultant BITWISE operation would have returned a value of 1 (a 1 appears in the 16 column in both binary numbers).

Hope this helps somebody somewhere someday.

Chloe