Skip to content

sys-sysdatabases-transact-sql.md

Latest commit

 

History

History
56 lines (50 loc) · 5.01 KB

sys-sysdatabases-transact-sql.md

File metadata and controls

56 lines (50 loc) · 5.01 KB
title sys.sysdatabases (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/15/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
sysdatabases_TSQL
sys.sysdatabases_TSQL
sys.sysdatabases
sysdatabases
dev_langs
TSQL
helpviewer_keywords
sys.sysdatabases compatibility view
sysdatabases system table
ms.assetid 60a93880-62f1-4eda-a886-f046706ba90c
caps.latest.revision 35
author BYHAM
ms.author rickbyh
manager jhubbard

sys.sysdatabases (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-asdw-pdw_md]

Contains one row for each database in an instance of [!INCLUDEmsCoName] [!INCLUDEssNoVersion]. When [!INCLUDEssNoVersion] is first installed, sysdatabases contains entries for the master, model, msdb, and tempdb databases.

Important

[!INCLUDEssnoteCompView]

Column name Data type Description
name sysname Database name
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 using ALTER DATABASE as noted:

1 = autoclose (ALTER DATABASE)

4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)

8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)

16 = torn page detection (ALTER DATABASE)

32 = loading

64 = pre recovery

128 = recovering

256 = not recovered

512 = offline (ALTER DATABASE)

1024 = read only (ALTER DATABASE)

2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)

4096 = single user (ALTER DATABASE)

32768 = emergency mode

65536 = CHECKSUM (ALTER DATABASE)

4194304 = autoshrink (ALTER DATABASE)

1073741824 = cleanly shutdown

Multiple bits can be ON at the same time.
status2 int 16384 = ANSI null default (ALTER DATABASE)

65536 = concat null yields null (ALTER DATABASE)

131072 = recursive triggers (ALTER DATABASE)

1048576 = default to local cursor (ALTER DATABASE)

8388608 = quoted identifier (ALTER DATABASE)

33554432 = cursor close on commit (ALTER DATABASE)

67108864 = ANSI nulls (ALTER DATABASE)

268435456 = ANSI warnings (ALTER DATABASE)

536870912 = full text enabled (set by using 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 for snapshot or transactional replication.

2 = Subscribed to a snapshot or transactional publication.

4 = Published for merge replication.

8 = Subscribed to a merge publication.

16 = Distribution database.
cmptlevel tinyint Compatibility level for the database. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
filename nvarchar(260) Operating-system path and name for the primary file for the database.

filename is visible to dbcreator, sysadmin, the database owner with CREATE ANY DATABASE permissions, or grantees that have any one of the following permissions: ALTER ANY DATABASE, CREATE ANY DATABASE, VIEW ANY DEFINITION. To return the path and file name, query the sys.sysfiles compatibility view, or the sys.database_files view.
version smallint Internal version number of the [!INCLUDEssNoVersion] code with which the database was created. [!INCLUDEssInternalOnly]

See Also

ALTER DATABASE (Transact-SQL)
Mapping System Tables to System Views (Transact-SQL)
Compatibility Views (Transact-SQL)