sysdatabases

Transact-SQL Reference

Transact-SQL Reference

sysdatabases

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 sp_dboption (read only, dbo use only, single user, and so on):

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

Multiple bits can be on at the same time.

status2 int 16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
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.