sysdbreg
A system table from the sys schema in master database. Exists in the master database only, contains a row for each registered database.
The column status2 was known as restat in MSSQL 2005. Cmptlevel is a new column which contains the compatibility level of the database in MSSQL 2008.
The values of status, restat and category are as described below.
| Hexadecimal |
Binary |
Decimal |
master.sys.sysdbreg.status |
master.sys.sysdbreg.restat |
master.sys.sysdbreg.category |
| 1 |
1 |
1 |
Is auto close on |
|
is published |
| 2 |
1 |
2 |
|
|
is subscribed |
| 4 |
1 |
4 |
|
is date correlation on |
is merge published |
| 8 |
1 |
8 |
*tempdb* |
|
|
| 10 |
1 |
16 |
|
|
is distributor |
| 20 |
1 |
32 |
Restoring |
|
is sync with backup |
| 40 |
1 |
64 |
|
is master key encrypted by server |
|
| 80 |
1 |
128 |
|
|
|
| 109 |
1 |
256 |
suspect |
|
|
| 200 |
1 |
512 |
|
is trustworthy on |
|
| 400 |
1 |
1.024 |
Readonly |
is db chaining on |
|
| 800 |
1 |
2.048 |
|
is numeric roundabort on |
|
| 1.000 |
1 |
4.096 |
|
is arithabort on |
|
| 2.000 |
1 |
8.192 |
|
is ansi padding on |
|
| 4.000 |
1 |
16.384 |
|
is ansi null default on |
|
| 8.000 |
1 |
32.768 |
|
|
|
| 10.000 |
1 |
65.536 |
default modeldb |
is concat null yields null on |
|
| 20.000 |
1 |
131.072 |
|
is recursive triggers on |
|
| 40.000 |
1 |
262.144 |
|
|
|
| 80.000 |
1 |
524.288 |
|
|
|
| 100.000 |
1 |
1.048.576 |
|
is local cursor default |
|
| 200.000 |
1 |
2.097.152 |
Is in standby |
|
|
| 400.000 |
1 |
4.194.304 |
Is auto shrink on |
|
|
| 800.000 |
1 |
8.388.608 |
is read commited snapsot on |
is quoted identifier on |
|
| 1.000.000 |
1 |
16.777.216 |
|
is auto create stats on |
|
| 2.000.000 |
1 |
33.554.432 |
|
is cursor close on commit on |
|
| 4.000.000 |
1 |
67.108.864 |
Is cleanly shutdown |
is ansi nulls on |
|
| 8.000.000 |
1 |
134.217.728 |
is suplemental logging enabled |
is parameterization forced |
|
| 10.000.000 |
1 |
268.435.456 |
|
is ansi warnings on |
|
| 20.000.000 |
1 |
536.870.912 |
|
is fulltext enabled |
|
| 40.000.000 |
1 |
1.073.741.824 |
|
is auto update stats on |
|
| 80.000.000 |
1 |
2.147.483.648 |
|
is auto update stats async on |
|
So, update sys.sysdbreg set status=256 where name='<database_name>' marks the database as suspect. Another little trick is to place your own database among the system databases. Because Managment Studio looks at the system databases names and at the distributor option the only way is to mark your database as distributor with:
update sys.sysdbreg set category=16 where name='<database_name>'
The easiest way to to combine multiple database options is to sum the decimal. So a database with an status 1280 is suspect and readonly, like wise for the categorie. See also http://www.g-productions.nl/artikel.php?artikel=8
|
