sys.databases (Transact-SQL)
Contains one row per database in the instance of Microsoft SQL Server.
If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
If a database is not ONLINE or AUTO_CLOSE is set to ON, the values of some columns may be NULL. If a database is OFFLINE, the corresponding row is not visible to low-privileged users. To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission or the CREATE DATABASE permission in the master database.
Column name
Data type
Description
name
sysname
Name of database, unique within an instance of SQL Server.
database_id
int
ID of the database, unique within an instance of SQL Server.
source_database_id
int
Non-NULL = ID of the source database of this database snapshot.
NULL = Not a database snapshot.
owner_sid
varbinary(85)
SID (Security-Identifier) of the external owner of the database, as registered to the server.
create_date
datetime
Date the database was created or renamed. For tempdb, this value changes every time the server restarts.
compatibility_level
tinyint
Integer corresponding to the version of SQL Server for which behavior is compatible:
70
80
90
NULL = Database is not online, or AUTO_CLOSE is set to ON.
collation_name
sysname
Collation for the database. Acts as the default collation in the database.
NULL = Database is not online or AUTO_CLOSE is set to ON.
user_access
tinyint
User-access setting:
0 = MULTI_USER specified
1 = SINGLE_USER specified
2 = RESTRICTED_USER specified
user_access_desc
nvarchar(60)
Description of user-access setting:
MULTI_USER
SINGLE_USER
RESTRICTED_USER
is_read_only
bit
1 = Database is READ_ONLY.
0 = Database is READ_WRITE.
is_auto_close_on
bit
1 = AUTO_CLOSE is ON.
0 = AUTO_CLOSE is OFF.
is_auto_shrink_on
bit
1 = AUTO_SHRINK is ON.
0 = AUTO_SHRINK is OFF.
state
tinyint
Database state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
state_desc
nvarchar(60)
Description of the database state:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE
For more information, see Database States.
is_in_standby
bit
Database is read-only for restore log.
is_cleanly_shutdown
bit
1 = Database shutdown cleanly; no recovery required on startup.
0 = Database did not shutdown cleanly; recovery is required on startup.
is_supplemental_logging_enabled
bit
1 = SUPPLEMENTAL_LOGGING is ON.
0 = SUPPLEMENTAL_LOGGING is OFF.
snapshot_isolation_state
tinyint
State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.
1 = Snapshot isolation state ON. Snapshot isolation is allowed.
2 = Snapshot isolation state is in transition to OFF state. All transactions have their modifications versioned. Cannot start new transactions using snapshot isolation. The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
3 = Snapshot isolation state is in transition to ON state. New transactions have their modifications versioned. Transactions cannot use snapshot isolation until the snapshot isolation state becomes 1 (ON). The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.
snapshot_isolation_state_desc
nvarchar(60)
Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
- OFF
- ON
- IN_TRANSITION_TO_ON
- IN_TRANSITION_TO_OFF
is_read_committed_snapshot_on
bit
1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.
0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.
recovery_model
tinyint
Recovery model selected:
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE
recovery_model_desc
nvarchar(60)
Description of recovery model selected:
FULL
BULK_LOGGED
SIMPLE
page_verify_option
tinyint
Setting of PAGE_VERIFY option:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM
page_verify_option_desc
nvarchar(60)
Description of PAGE_VERIFY option setting:
NONE.TORN_PAGE_DETECTION
CHECKSUM
is_auto_create_stats_on
bit
1 = AUTO_CREATE_STATISTICS is ON.
0 = AUTO_CREATE_STATISTICS is OFF.
is_auto_update_stats_on
bit
1 = AUTO_UPDATE_STATISTICS is ON.
0 = AUTO_UPDATE_STATISTICS is OFF.
is_auto_update_stats_async_on
bit
1 = AUTO_UPDATE_STATISTICS_ASYNC is ON.
0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF.
is_ansi_null_default_on
bit
1 = ANSI_NULL_DEFAULT is ON.
0 = ANSI_NULL_DEFAULT is OFF.
is_ansi_nulls_on
bit
1 = ANSI_NULLS is ON.
0 = ANSI_NULLS is OFF.
is_ansi_padding_on
bit
1 = ANSI_PADDING is ON.
0 = ANSI_PADDING is OFF.
is_ansi_warnings_on
bit
1 = ANSI_WARNINGS is ON.
0 = ANSI_WARNINGS is OFF.
is_arithabort_on
bit
1 = ARITHABORT is ON.
0 = ARITHABORT is OFF.
is_concat_null_yields_null_on
bit
1 = CONCAT_NULL_YIELDS_NULL is ON.
0 = CONCAT_NULL_YIELDS_NULL is OFF.
is_numeric_roundabort_on
bit
1 = NUMERIC_ROUNDABORT is ON.
0 = NUMERIC_ROUNDABORT is OFF.
is_quoted_identifier_on
bit
1 = QUOTED_IDENTIFIER is ON.
0 = QUOTED_IDENTIFIER is OFF.
is_recursive_triggers_on
bit
1 = RECURSIVE_TRIGGERS is ON.
0 = RECURSIVE_TRIGGERS is OFF.
is_cursor_close_on_commit_on
bit
1 = CURSOR_CLOSE_ON_COMMIT is ON.
0 = CURSOR_CLOSE_ON_COMMIT is OFF.
is_local_cursor_default
bit
1 = CURSOR_DEFAULT is local.
0 = CURSOR_DEFAULT is global.
is_fulltext_enabled
bit
1 = Full-text is enabled for the database.
0 = Full-text is disabled for the database.
is_trustworthy_on
bit
1 = Database has been marked trustworthy.
0 = Database has not been marked trustworthy.
is_db_chaining_on
bit
1 = Cross-database ownership chaining is ON.
0 = Cross-database ownership chaining is OFF.
is_parameterization_forced
bit
1 = Parameterization is FORCED.
0 = Parameterization is SIMPLE.
is_master_key_encrypted_by_server
bit
1 = Database has an encrypted master key.
0 = Database does not have an encrypted master key.
is_published
bit
1 = Database is a publication database in a transactional or snapshot replication topology.
0 = Is not a publication database.
is_subscribed
bit
1 = Database is a subscription database in a replication topology.
0 = Is not a subscription database.
is_merge_published
bit
1 = Database is a publication database in a merge replication topology.
0 = Is not a publication database in a merge replication topology.
is_distributor
bit
1 = Database is the distribution database for a replication topology.
0 = Is not the distribution database for a replication topology.
is_sync_with_backup
bit
1 = Database is marked for replication synchronization with backup.
0 = Is not marked for replication synchronization with backup.
service_broker_guid
uniqueidentifier
Identifier of the service broker for this database. Used as the broker_instance of the target in the routing table.
is_broker_enabled
bit
1 = The broker in this database is currently sending and receiving messages.
0 = All sent messages will stay on the transmission queue and received messages will not be put on queues in this database.
By default, restored or attached databases have the broker disabled. The exception to this is database mirroring where the broker is enabled after failover.
log_reuse_wait
tinyint
Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = Other (transient)
log_reuse_wait_desc
nvarchar(60)
Description of reuse of transaction log space is currently waiting on one of the following:
NOTHING
CHECKPOINT
LOG_BACKUP
.gif "ms178534.note(en-US,SQL.90).gif")
Note:
If the reason is LOG_BACKUP, it may take two backups to actually free the space.
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT
For more information, see Factors That Can Delay Log Truncation.
is_date_correlation_on
bit
1 = DATE_CORRELATION_OPTIMIZATION is ON.
0 = DATE_CORRELATION_OPTIMIZATION is OFF.
See Also
Reference
ALTER DATABASE (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.database_recovery_status (Transact-SQL)
Databases and Files Catalog Views (Transact-SQL)