sysdatabases

master database only

Description

sysdatabases contains one row for each database in Adaptive Server. When Adaptive Server is installed, sysdatabases contains entries for the master database, the model database, the sybsystemprocs database, and the tempdb database. If you have installed auditing, it also contains an entry for the sybsecurity database.

Columns

The columns for sysdatabases are:

Name	Datatype	Description
name	sysname	Name of the database
dbid	smallint	Database ID
suid	int	Server user ID of database owner
status	smallint	Control bits; those that the user can set with sp_dboption are so indicated in Table 12-5
version	smallint	Unused
logptr	int	Pointer to transaction log
crdate	datetime	Creation date
dumptrdate	datetime	Date of the last dump transaction
status2	intn	Additional control bits. See Table 12-6
audflags	intn	Audit settings for database
deftabaud	intn	Bit-mask that defines default audit settings for tables
defvwaud	intn	Bit-mask that defines default audit settings for views
defpraud	intn	Bit-mask that defines default audit settings for stored procedures
def_remote_type	smallint	Identifies the default object type to be used for remote tables if no storage location is provided via the stored procedure sp_addobjectdef
def_remote_loc	varchar(255)	Identifies the default storage location to be used for remote tables if no storage location is provided via the stored procedure sp_addobjectdef
status3	intn	Additional control bits.
status4	intn	Additional control bits.

Table 12-5 lists the bit representations for the status column.

**Table 12-5: status control bits in the sysdatabases table**
Decimal	Hex	Status
4	0x04	select into/bulkcopy Can be set by user
8	0x08	trunc log on chkpt Can be set by user
16	0x10	no chkpt on recovery Can be set by user
32	0x20	Database created with for load option, or crashed while loading database, instructs recovery not to proceed
256	0x100	Database suspect Not recovered Cannot be opened or used Can be dropped only with dbcc dbrepair
512	0x200	ddl in tran Can be set by user
1024	0x400	read only Can be set by user
2048	0x800	dbo use only Can be set by user
4096	0x1000	single user Can be set by user
8192	0x2000	allow nulls by default Can be set by user

Table 12-6 lists the bit representations for the status2 column.

**Table 12-6: status2 control bits in the sysdatabases table**
Decimal	Hex	Status
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; can be 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	Internal use – Database is being recovered
128	0x0080	Database has suspect pages
256†	0x0100	Table structure written to disk
512	0x0200	Database is in the process of being upgraded
1024	0x0400	Database brought online for standby access
-32768	0xFFFF8000	Database has some portion of the log which is not on a log-only device

† If this bit appears after recovery completes, the server may be under-configured for open databases. Use sp_configure to increase this parameter.

Indexes

Unique clustered index On name

Unique nonclustered index On dbid