sybase 数据库状态判断

由于数据库较多,想通过查询某个系统表来得知数据库的情况,如数据库是否处于在线状态 我现在是 use  xxx/ go 一个个登录试的,感觉比较原始,谢谢.

==============================================================

Table 1-6: Status control bits in the sysdatabases table[tr]Decimal
Hex
Status
[/tr]

1	0x01	Upgrade started on this database
2	0x02	Upgrade has been successful
4	0x04	selectinto/bulkcopy Can be set by user
8	0x08	trunclog on chkpt Can be set by user
16	0x10	no chkpton recovery Can be set by user
32	0x20	Database created with for load option,or crashed while loading database, instructs recovery not to proceed
64	0x04	Recovery started for all databases tobe recovered
256	0x100	Databasesuspect Not recovered Cannot be opened or used Can be dropped only with dbcc dbrepair
512	0x200	ddl intran Can be set by user
1024	0x400	read only Can be set by user
2048	0x800	dbo useonly Can be set by user
4096	0x1000	singleuser Can be set by user
8192	0x2000	allownulls by default Can be set by user

Table 1-7 liststhe bit representations for the status2 column.
Table 1-7: status2 control bits in the sysdatabases table[tr]Decimal
Hex
Status
[/tr]

1	0x0001	abort tran on log full;can be set by user
2	0x0002	no free space acctg;can be set by user
4	0x0004	auto identity; canbe set by user
8	0x0008	identity in nonunique index;can be set by user
16	0x0010	Database is offline
32	0x0020	Database is offline until recovery completes
64	0x0040	The table has an auto identity feature,and a unique constraint on the identity column
128	0x0080	Database has suspect pages
256	0x0100	Table structure written to disk If thisbit appears after recovery completes, the server may be under-configuredfor open databases. Use sp_configure to increasethis parameter.
512	0x0200	Database is in the process of being upgraded
1024	0x0400	Database brought online for standby access
2048	0x0800	When set by the user, prevents cross-databaseaccess via an alias mechanism
-32768	0xFFFF8000	Database has some portion of the logwhich is not on a log-only device

Table 1-8 liststhe bit representations for the status3 column.
Table 1-8: status3 control bits in the sysdatabasestable[tr]Decimal
Hex
Status
[/tr]

1	0x0001	Database is a user-created proxy database.
2	0x0002	Database is a proxy database createdby high availability.
4	0x0004	Database has a proxy database createdby high availability.
8	0x0008	Disallow access to the database, sincedatabase is being shut down.
16	0x0010	Database is a failed-over database.
32	0x0020	Database is a mounted database of thetype master.
64	0x0040	Database is a mounted database.
128	0x0080	Writes to the database are blocked bythe quiesce database command.
256	0x0100	User-created tempdb.
512	0x0200	Disallow external access to databasein the server in failed-over state.
1024	0x0400	User-provided option to enableor disable asynchronous logging service threads. User enables thisthrough sp_dboption enbaleasync logging service option set to true on a particulardatabase.
4096	0x1000	Database has been shut down successfully.
8192	0x2000	A drop database isin progress.

==============================================================

sp_helpdb

==============================================================

其实很简单，select status,status2 from master..sysdatabases表，根据andkylee提供的信息，处理一下，另外你可以参考sybsystemprocs库中sp_helpdb的原始代码，很方便就实现

==============================================================

select name,status from sysdatabases
name                           status
------------------------------ ------
master                              0
model                               0
sybsystemdb                         0
sybsystemprocs                      0
tempdb                              4
test123                               12

test123                     中         12代表什么意思呢？

==============================================================

QUOTE:

原帖由 andkylee 于 2010-1-16 16:29 发表
12=4+8=select into/bulkcopy + truncate log on checkpoint

谢谢，可以说明下 select into/bulkcopy 的意思吗？

==============================================================
你试下select into或bcp就知道了

转自：http://bbs.chinaunix.net/thread-1637947-1-1.html