Chapter 1: System Tables

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 1-6
version	smallint	Unused
logptr	int	Pointer to transaction log
crdate	datetime	Creation date
dumptrdate	datetime	Date of the last dump transaction
status2	smallint null	Additional control bit (see Table 1-7)
audflags	int null	Audit settings for database
deftabaud	int null	Bit-mask that defines default audit settings for tables
defvwaud	int null	Bit-mask that defines default audit settings for views
defpraud	int null	Bit-mask that defines default audit settings for stored procedures
def_remote_type	smallint null	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(349) null	Identifies the default storage location to be used for remote tables if no storage location is provided via the stored procedure sp_addobjectdef
status3	int null	Additional control bits
status4	int null	Additional control bits
audflags2	varbinary(16) null	Reserved for future use

Table 1-6 lists the bit representations for the status column.

**Table 1-6: Status control bits in the sysdatabases table**
Decimal	Hex	Status
1	0x01	Upgrade started on this database
2	0x02	Upgrade has been successful
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
64	0x04	Recovery started for all databases to be recovered
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 1-7 lists the bit representations for the status2 column.

**Table 1-7: 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	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 this bit appears after recovery completes, the server may be under-configured for open databases. Use sp_configure to increase this 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-database access via an alias mechanism
-32768	0xFFFF8000	Database has some portion of the log which is not on a log-only device

Table 1-8 lists the bit representations for the status3 column.

**Table 1-8: status3 control bits in the sysdatabases table**
Decimal	Hex	Status
1	0x0001	Database is a user-created proxy database.
2	0x0002	Database is a proxy database created by high availability.
4	0x0004	Database has a proxy database created by high availability.
8	0x0008	Disallow access to the database, since database is being shut down.
16	0x0010	Database is a failed-over database.
32	0x0020	Database is a mounted database of the type master.
64	0x0040	Database is a mounted database.
128	0x0080	Writes to the database are blocked by the quiesce database command.
256	0x0100	User-created tempdb.
512	0x0200	Disallow external access to database in the server in failed-over state.
1024	0x0400	User-provided option to enable or disable asynchronous logging service threads. User enables this through sp_dboption enbale async logging service option set to true on a particular database.
4096	0x1000	Database has been shut down successfully.
8192	0x2000	A drop database is in progress.

Indexes

Unique clustered index on name
Nonclustered index on dbid

View this book as PDF