Export (0) Print
Expand All
Expand Minimize

sysdatabases

SQL Server 2000

  Topic last updated -- July 2003

Contains one row for each database on Microsoft® SQL Server™. When SQL Server is initially installed, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases. This table is stored only in the master database.

Column name Data type Description
name sysname Name of the database.
dbid smallint Database ID.
sid varbinary(85) System ID of the database creator.
mode smallint Used internally for locking a database while it is being created.
status int Status bits, some of which can be set by the user with ALTER DATABASE (read only, offline, single user, and so on):

1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.

Multiple bits can be on at the same time.

status2 int 16384 = ANSI null default; set with ALTER DATABASE.
65536 = concat null yields null , set with ALTER DATABASE.
131072 = recursive triggers, set with ALTER DATABASE.
1048576 = default to local cursor, set with ALTER DATABASE.
8388608 = quoted identifier, set with ALTER DATABASE.
33554432 = cursor close on commit, set with ALTER DATABASE.
67108864 = ANSI nulls, set with ALTER DATABASE.
268435456 = ANSI warnings, set with ALTER DATABASE.
536870912 = full text enabled, set with sp_fulltext_database.
crdate datetime Creation date.
reserved datetime Reserved for future use.
category int Contains a bitmap of information used for replication:

1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.

cmptlevel tinyint Compatibility level for the database. For more information, see sp_dbcmptlevel.
filename nvarchar(260) Operating-system path and name for the database's primary file.
version smallint Internal version number of the SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing.

Show:
© 2014 Microsoft