sys.databases (Transact-SQL)

 

Updated: May 20, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Contains one row per database in the instance of SQL Server.

If a database is not ONLINE, or AUTO_CLOSE is set to ON and the database is closed, 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.

Columns that are relevant to Active Geo-Replication for SQL Database: is_ready_only, state, and state_desc.

Column nameData typeDescription
namesysnameName of database, unique within an instance of SQL Server or within a Azure SQL Database server.
database_idintID of the database, unique within an instance of SQL Server or within a Azure SQL Database server.
source_database_idintNon-NULL = ID of the source database of this database snapshot.

NULL = Not a database snapshot.
owner_sidvarbinary(85)SID (Security-Identifier) of the external owner of the database, as registered to the server. For information about who can own a database, see the ALTER AUTHORIZATION for databases section of ALTER AUTHORIZATION.
create_datedatetimeDate the database was created or renamed. For tempdb, this value changes every time the server restarts.
compatibility_leveltinyintInteger corresponding to the version of SQL Server for which behavior is compatible:

 Value | Applies to

70 | SQL Server 2008 through SQL Server 2008 R2

80 |
                      SQL Server 2008 through SQL Server 2008 R2

90 |SQL Server 2008 through SQL Server 2012

100 |SQL Server 2008 through SQL Server 2016 and Azure SQL Database

110 | SQL Server 2012 through SQL Server 2016 and Azure SQL Database

120 |
                      SQL Server 2014 through SQL Server 2016 and Azure SQL Database

130 |
                      SQL Server 2016 through SQL Server 2016

NULL
collation_namesysnameCollation for the database. Acts as the default collation in the database.

NULL = Database is not online or AUTO_CLOSE is set to ON and the database is closed.
user_accesstinyintUser-access setting:

0 = MULTI_USER specified

1 = SINGLE_USER specified

2 = RESTRICTED_USER specified
user_access_descnvarchar(60)Description of user-access setting.
is_read_onlybit1 = Database is READ_ONLY

0 = Database is READ_WRITE
is_auto_close_onbit1 = AUTO_CLOSE is ON

0 = AUTO_CLOSE is OFF
is_auto_shrink_onbit1 = AUTO_SHRINK is ON

0 = AUTO_SHRINK is OFF
statetinyintValue | Applies to

0 = ONLINE |

1 = RESTORING |

2 = RECOVERING | SQL Server 2008 through SQL Server 2016

3 = RECOVERY_PENDING | SQL Server 2008 through SQL Server 2016

4 = SUSPECT |

5 = EMERGENCY | SQL Server 2008 through SQL Server 2016

6 = OFFLINE | SQL Server 2008 through SQL Server 2016

7 = COPYING | Azure SQL Database

10 = OFFLINE_SECONDARY | Azure SQL Database

 Note: A database that has just come online is not necessarily ready to accept connections. To identify when a database can accept connections, query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value. For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.
state_descnvarchar(60)Description of the database state.
is_in_standbybitDatabase is read-only for restore log.
is_cleanly_shutdownbit1 = Database shut down cleanly; no recovery required on startup

0 = Database did not shut down cleanly; recovery is required on startup
is_supplemental_logging_enabledbit1 = SUPPLEMENTAL_LOGGING is ON

0 = SUPPLEMENTAL_LOGGING is OFF
snapshot_isolation_statetinyintState 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_descnvarchar(60)Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option.
is_read_committed_snapshot_onbit1 = 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_modeltinyintRecovery model selected:

1 = FULL

2 = BULK_LOGGED

3 = SIMPLE
recovery_model_descnvarchar(60)Description of recovery model selected.
page_verify_optiontinyintSetting of PAGE_VERIFY option:

0 = NONE

1 = TORN_PAGE_DETECTION

2 = CHECKSUM
page_verify_option_descnvarchar(60)Description of PAGE_VERIFY option setting.
is_auto_create_stats_onbit1 = AUTO_CREATE_STATISTICS is ON

0 = AUTO_CREATE_STATISTICS is OFF
is_auto_create_stats_incremental_onbitIndicates the default setting for the incremental option of auto stats.

0 = auto create stats are non-incremental

1 = auto create stats are incremental if possible

 Applies to: SQL Server 2014 through SQL Server 2016.
is_auto_update_stats_onbit1 = AUTO_UPDATE_STATISTICS is ON

0 = AUTO_UPDATE_STATISTICS is OFF
is_auto_update_stats_async_onbit1 = AUTO_UPDATE_STATISTICS_ASYNC is ON

0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF
is_ansi_null_default_onbit1 = ANSI_NULL_DEFAULT is ON

0 = ANSI_NULL_DEFAULT is OFF
is_ansi_nulls_onbit1 = ANSI_NULLS is ON

0 = ANSI_NULLS is OFF
is_ansi_padding_onbit1 = ANSI_PADDING is ON

0 = ANSI_PADDING is OFF
is_ansi_warnings_onbit1 = ANSI_WARNINGS is ON

0 = ANSI_WARNINGS is OFF
is_arithabort_onbit1 = ARITHABORT is ON

0 = ARITHABORT is OFF
is_concat_null_yields_null_onbit1 = CONCAT_NULL_YIELDS_NULL is ON

0 = CONCAT_NULL_YIELDS_NULL is OFF
is_numeric_roundabort_onbit1 = NUMERIC_ROUNDABORT is ON

0 = NUMERIC_ROUNDABORT is OFF
is_quoted_identifier_onbit1 = QUOTED_IDENTIFIER is ON

0 = QUOTED_IDENTIFIER is OFF
is_recursive_triggers_onbit1 = RECURSIVE_TRIGGERS is ON

0 = RECURSIVE_TRIGGERS is OFF
is_cursor_close_on_commit_onbit1 = CURSOR_CLOSE_ON_COMMIT is ON

0 = CURSOR_CLOSE_ON_COMMIT is OFF
is_local_cursor_defaultbit1 = CURSOR_DEFAULT is local

0 = CURSOR_DEFAULT is global
is_fulltext_enabledbit1 = Full-text is enabled for the database

0 = Full-text is disabled for the database
is_trustworthy_onbit1 = Database has been marked trustworthy

0 = Database has not been marked trustworthy
is_db_chaining_onbit1 = Cross-database ownership chaining is ON

0 = Cross-database ownership chaining is OFF
is_parameterization_forcedbit1 = Parameterization is FORCED

0 = Parameterization is SIMPLE
is_master_key_encrypted_by_serverbit1 = Database has an encrypted master key

0 = Database does not have an encrypted master key
is_query_store_onbit1 = The query store is enable for this database. Check sys.database_query_store_options to view the query store status.

0 = The query store is not enabled

 Applies to: SQL Server (SQL Server 2016 through current version).
is_publishedbit1 = Database is a publication database in a transactional or snapshot replication topology

0 = Is not a publication database
is_subscribedbitThis column is not used. It will always return 0, regardless of the subscriber status of the database.
is_merge_publishedbit1 = Database is a publication database in a merge replication topology

0 = Is not a publication database in a merge replication topology
is_distributorbit1 = Database is the distribution database for a replication topology

0 = Is not the distribution database for a replication topology
is_sync_with_backupbit1 = Database is marked for replication synchronization with backup

0 = Is not marked for replication synchronization with backup
service_broker_guiduniqueidentifierIdentifier of the service broker for this database. Used as the broker_instance of the target in the routing table.
is_broker_enabledbit1 = 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_waittinyintReuse of transaction log space is currently waiting on one of the following as of the last checkpoint:

 Value 0 = Nothing

 Applies to

 Value 1 = Checkpoint (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.)

 Applies to SQL Server 2008 through SQL Server 2016

 Value 2 = Log Backup

 Applies to SQL Server 2008 through SQL Server 2016

 Value 3 = Active backup or restore

 Applies to SQL Server 2008 through SQL Server 2016

 Value 4 = Active transaction

 Applies to SQL Server 2008 through SQL Server 2016

 Value 5 = Database mirroring

 Applies to SQL Server 2008 through SQL Server 2016

 Value 6 = Replication

 Applies to SQL Server 2008 through SQL Server 2016

 Value 7 = Database snapshot creation

 Applies to SQL Server 2008 through SQL Server 2016

 Value 8 = Log scan

 Applies to

 Value 9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.

 Applies to SQL Server 2012 through SQL Server 2016. In earlier versions of SQL Server, 9 = Other (Transient).

 Value 10 = For internal use only

 Applies to SQL Server 2012 through SQL Server 2016

 Value 11 = For internal use only

 Applies to SQL Server 2012 through SQL Server 2016

 Value 12 = For internal use only

 Applies to SQL Server 2012 through SQL Server 2016

 Value 13 = Oldest page

 Applies to SQL Server 2012 through SQL Server 2016

 Value 14 = Other

 Applies to SQL Server 2012 through SQL Server 2016

 Value 16 = XTP_CHECKPOINT (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.)

 Applies to SQL Server 2014 through SQL Server 2016
log_reuse_wait_descnvarchar(60)Description of reuse of transaction log space is currently waiting on as of the last checkpoint.
is_date_correlation_onbit1 = DATE_CORRELATION_OPTIMIZATION is ON

0 = DATE_CORRELATION_OPTIMIZATION is OFF
is_cdc_enabledbit1 = Database is enabled for change data capture. For more information, see sys.sp_cdc_enable_db (Transact-SQL).
is_encryptedbitIndicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause). Can be one of the following values:

1 = Encrypted

0 = Not Encrypted

For more information about database encryption, see Transparent Data Encryption (TDE).

If the database is in the process of being decrypted, is_encrypted shows a value of 0. You can see the state of the encryption process by using the sys.dm_database_encryption_keys dynamic management view.
is_honor_broker_priority_onbitIndicates whether the database honors conversation priorities (reflects the state last set by using the ALTER DATABASE SET HONOR_BROKER_PRIORITY clause). Can be one of the following values:

1 = HONOR_BROKER_PRIORITY is ON

0 = HONOR_BROKER_PRIORITY is OFF
replica_iduniqueidentifierUnique identifier of the local Always On Availability Groups availability replica of the availability group, if any, in which the database is participating.

NULL = database is not part of an availability replica of in availability group.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
group_database_iduniqueidentifierUnique identifier of the database within an Always On availability group, if any, in which the database is participating. group_database_id is the same for this database on the primary replica and on every secondary replica on which the database has been joined to the availability group.

NULL = database is not part of an availability replica in any availability group.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
resource_pool_idintThe id of the resource pool that is mapped to this database. This resource pool controls total memory available to memory-optimized tables in this database.

 Applies to: SQL Server 2014 through SQL Server 2016
default_language_lcidsmallintIndicates the local id (lcid) of the default language of a contained database.

 Note Functions as the Configure the default language Server Configuration Option of sp_configure. This value is null for a non-contained database.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
default_language_namenvarchar(128)Indicates the default language of a contained database.

This value is null for a non-contained database.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
default_fulltext_language_lcidintIndicates the local id (lcid) of the default fulltext language of the contained database.

 Note Functions as the default Configure the default full-text language Server Configuration Option of sp_configure. This value is null for a non-contained database.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
default_fulltext_language_namenvarchar(128)Indicates the default fulltext language of the contained database.

This value is null for a non-contained database.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
is_nested_triggers_onbitIndicates whether or not nested triggers are allowed in the contained database.

0 = nested triggers are not allowed

1 = nested triggers are allowed

 Note Functions as the Configure the nested triggers Server Configuration Option of sp_configure. This value is null for a non-contained database. See sys.configurations (Transact-SQL) for further information.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
is_transform_noise_words_onbitIndicates whether or noise words should be transformed in the contained database.

0 = noise words should not be transformed.

1 = noise words should be transformed.

 Note Functions as the transform noise words Server Configuration Option of sp_configure. This value is null for a non-contained database. See sys.configurations (Transact-SQL) for further information.

 Applies to: SQL Server 2012 through SQL Server 2016
two_digit_year_cutoffsmallintIndicates a value of a number between 1753 and 9999 to represent the cutoff year for interpreting two-digit years as four-digit years.

 Note Functions as the Configure the two digit year cutoff Server Configuration Option of sp_configure. This value is null for a non-contained database. See sys.configurations (Transact-SQL) for further information.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
containmenttinyint not nullIndicates the containment status of the database.

 Value | Applies to

0 = database containment is off.

 SQL Server 2012 through SQL Server 2016, Azure SQL Database

1 = database is in partial containment

 SQL Server 2012 through SQL Server 2016
containment_descnvarchar(60) not nullIndicates the containment status of the database.

NONE = legacy database (zero containment)

PARTIAL = partially contained database

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
target_recovery_time_in_secondsintThe estimated time to recover the database, in seconds. Nullable.

 Applies to: SQL Server 2012 through SQL Server 2016, Azure SQL Database
delayed_durabilityintThe delayed durability setting:

0 = DISABLED

1 = ALLOWED

2 = FORCED

For more information, see Control Transaction Durability.

 Applies to: SQL Server 2014 through SQL Server 2016, Azure SQL Database.
delayed_durability_descnvarchar(60)The delayed durability setting:

DISABLED

ALLOWED

FORCED

 Applies to: SQL Server 2014 through SQL Server 2016, Azure SQL Database.

 Applies to: SQL Server 2014 through SQL Server 2016.
is_memory_optimized_elevate_to_snapshot_onbitMemory-optimized tables are accessed using SNAPSHOT isolation when the session setting TRANSACTION ISOLATION LEVEL is set to a lower isolation level, READ COMMITTED or READ UNCOMMITTED.

1 = Minimum isolation level is SNAPSHOT.

0 = Isolation level is not elevated.
is_federation_memberbitIndicates if the database is a member of a federation.

 Applies to: Azure SQL Database
is_remote_data_archive_enabledbitIndicates whether the database is stretched.

0 = The database is not Stretch-enabled.

1 = The database is Stretch-enabled.

 Applies to: SQL Server 2016 through SQL Server 2016

For more information, see Stretch Database.
is_mixed_page_allocation_onbitIndicates whether tables and indexes in the database can allocate initial pages from mixed extents.

0 = Tables and indexes in the database always allocate initial pages from uniform extents.

1 = Tables and indexes in the database can allocate initial pages from mixed extents.

 Applies to: SQL Server 2016 through SQL Server 2016

For more information, see the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE SET Options (Transact-SQL).

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.

System_CAPS_ICON_important.jpg Important


By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the VIEW ANY DATABASE permission for individual logins.

In SQL Database, this view is available in the master database and in user databases. In the master database, this view returns the information on the master database and all user databases on the server. In a user database, this view returns information only on the current database and the master database.

Use the sys.databases view in the master database of the SQL Database server where the new database is being created. After the database copy starts, you can query the sys.databases and the sys.dm_database_copies views from the master database of the destination server to retrieve more information about the copying progress.

A. Query the sys.databases view

The following example returns a few of the columns available in the sys.databases view.

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc  
FROM sys.databases;  

B. Check the copying status in SQL Database

The following example queries the sys.databases and sys.dm_database_copies views to return information about a database copy operation.

Applies to: Azure SQL Database
-- Execute from the master database.  
SELECT a.name, a.state_desc, b.start_date, b.modify_date, b.percentage_complete  
FROM sys.databases AS a  
INNER JOIN sys.dm_database_copies AS b ON a.database_id = b.database_id  
WHERE a.state = 7;  

ALTER DATABASE (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.database_recovery_status (Transact-SQL)
Databases and Files Catalog Views (Transact-SQL)
sys.dm_database_copies (Azure SQL Database)

Community Additions

ADD
Show: