sys.databases (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

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

If a database isn't ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns can be NULL. If a database is OFFLINE, the corresponding row isn't 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 or within a Azure SQL Database logical server.
database_id	int	ID of the database, unique within an instance of SQL Server. In Azure SQL Database, the values are unique within a logical server, and are joinable with sys.database_service_objectives on the database_id column, but not with other system views where the database_id column is present. For details, see DB_ID.
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. For information about who can own a database, see ALTER AUTHORIZATION for databases.
create_date	datetime	Date the database was created or renamed. For tempdb, this value changes every time the server restarts. The create_date does not change when a rename operation is performed on Azure SQL Database.
compatibility_level	tinyint	Integer corresponding to the version of SQL Server for which behavior is compatible. 70 - SQL Server 7.0 through SQL Server 2008 (10.0.x) 80 - SQL Server 2000 (8.x) through SQL Server 2008 R2 (10.50.x) 90 - SQL Server 2008 (10.0.x) through SQL Server 2012 (11.x) 100 - SQL Server 2008 (10.0.x) and later versions, and Azure SQL Database 110 - SQL Server 2012 (11.x) and later versions, and Azure SQL Database 120 - SQL Server 2014 (12.x) and later versions, and Azure SQL Database 130 - SQL Server 2016 (13.x) and later versions, and Azure SQL Database 140 - SQL Server 2017 (14.x) and later versions, and Azure SQL Database 150 - SQL Server 2019 (15.x) and later versions, and Azure SQL Database 160 - SQL Server 2022 (16.x) and later versions, and Azure SQL Database
collation_name	sysname	Collation for the database. Acts as the default collation in the database. NULL = Database isn't online or AUTO_CLOSE is set to ON and the database is closed.
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.
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	Value 0 = ONLINE 1 = RESTORING 2 = RECOVERING 1 3 = RECOVERY_PENDING 1 4 = SUSPECT 5 = EMERGENCY 1 6 = OFFLINE 1 7 = COPYING 2 10 = OFFLINE_SECONDARY 2 Note: For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states. 1 Applies to: SQL Server 2008 (10.0.x) and later versions, and Azure SQL Database 2 Applies to: Azure SQL Database Active Geo-Replication
state_desc	nvarchar(60)	Description of the database state. See state.
is_in_standby	bit	Database is read-only for restore log.
is_cleanly_shutdown	bit	1 = Database shut down cleanly; no recovery required on startup 0 = Database didn't shut down 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. Can't 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 can't 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.
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 don't 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.
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.
is_auto_create_stats_on	bit	1 = AUTO_CREATE_STATISTICS is ON 0 = AUTO_CREATE_STATISTICS is OFF
is_auto_create_stats_incremental_on	bit	Indicates 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 (12.x) and later versions.
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 hasn't been marked trustworthy By default, restored or attached databases have the trustworthy not enabled.
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 doesn't have an encrypted master key
is_query_store_on	bit	1 = The Query Store is enabled for this database. Check sys.database_query_store_options to view the Query Store status. 0 = The Query Store isn't enabled Applies to: SQL Server 2016 (13.x) and later versions.
is_published	bit	1 = Database is a publication database in a transactional or snapshot replication topology 0 = Isn't a publication database
is_subscribed	bit	This column isn't used. It will always return 0, regardless of the subscriber status of the database.
is_merge_published	bit	1 = Database is a publication database in a merge replication topology 0 = Isn't a publication database in a merge replication topology
is_distributor	bit	1 = Database is the distribution database for a replication topology 0 = Isn't the distribution database for a replication topology
is_sync_with_backup	bit	1 = Database is marked for replication synchronization with backup 0 = Isn't 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 won't 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 as of the last checkpoint. For more detailed explanations of these values, see The transaction log. Value 0 = Nothing 1 = Checkpoint (When a database has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint) 1 2 = Log Backup 1 3 = Active backup or restore 1 4 = Active transaction 1 5 = Database mirroring 1 6 = Replication 1 7 = Database snapshot creation 1 8 = Log scan 9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. 2 9 = Other (Transient) 3 10 = For internal use only 2 11 = For internal use only 2 12 = For internal use only 2 13 = Oldest page 2 14 = Other 2 16 = XTP_CHECKPOINT (When a database has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint) 4 17 = sLog scanning when Accelerated Database Recovery is used 5 1 Applies to: SQL Server 2008 (10.0.x) and later versions 2 Applies to: SQL Server 2012 (11.x) and later versions 3 Applies to: SQL Server 2008 R2 (10.50.x) and earlier versions 4 Applies to: SQL Server 2014 (12.x) and later versions 5 Applies to: SQL Server 2019 (15.x) and later versions
log_reuse_wait_desc	nvarchar(60)	Description of reuse of transaction log space is currently waiting on as of the last checkpoint. Possible values: NOTHING CHECKPOINT LOG_BACKUP ACTIVE_BACKUP_OR_RESTORE ACTIVE_TRANSACTION DATABASE_MIRRORING REPLICATION DATABASE_SNAPSHOT_CREATION LOG_SCAN AVAILABILITY_REPLICA OLDEST_PAGE XTP_CHECKPOINT SLOG_SCAN
is_date_correlation_on	bit	1 = DATE_CORRELATION_OPTIMIZATION is ON 0 = DATE_CORRELATION_OPTIMIZATION is OFF
is_cdc_enabled	bit	1 = Database is enabled for change data capture. For more information, see sys.sp_cdc_enable_db (Transact-SQL).
is_encrypted	bit	Indicates 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_on	bit	Indicates 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 By default, restored or attached databases have the broker priority off.
replica_id	uniqueidentifier	Unique identifier of the local Always On availability groups availability replica of the availability group, if any, in which the database is participating. NULL = database isn't part of an availability replica of in availability group. Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
group_database_id	uniqueidentifier	Unique 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 isn't part of an availability replica in any availability group. Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
resource_pool_id	int	The 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 (12.x) and later versions
default_language_lcid	smallint	Indicates 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 (11.x) and later versions, and Azure SQL Database
default_language_name	nvarchar(128)	Indicates the default language of a contained database. This value is NULL for a non-contained database. Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
default_fulltext_language_lcid	int	Indicates the locale 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 (11.x) and later versions, and Azure SQL Database
default_fulltext_language_name	nvarchar(128)	Indicates the default fulltext language of the contained database. This value is NULL for a non-contained database. Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
is_nested_triggers_on	bit	Indicates whether or not nested triggers are allowed in the contained database. 0 = nested triggers aren't 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. For more information, see sys.configurations (Transact-SQL). Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
is_transform_noise_words_on	bit	Indicates whether or noise words should be transformed in the contained database. 0 = noise words shouldn't 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. For more information, see sys.configurations (Transact-SQL). Applies to: SQL Server 2012 (11.x) and later versions
two_digit_year_cutoff	smallint	Indicates 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. For more information, see sys.configurations (Transact-SQL). Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
containment	tinyint	Indicates the containment status of the database. Not nullable. 0 = database containment is off. Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database. 1 = database is in partial containment Applies to: SQL Server 2012 (11.x) and later versions
containment_desc	nvarchar(60)	Indicates the containment status of the database. Not nullable. NONE = legacy database (zero containment) PARTIAL = partially contained database Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
target_recovery_time_in_seconds	int	The estimated time to recover the database, in seconds. Nullable. Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database
delayed_durability	int	The delayed durability setting: 0 = DISABLED 1 = ALLOWED 2 = FORCED For more information, see Control Transaction Durability. Applies to: SQL Server 2014 (12.x) and later versions, and Azure SQL Database.
delayed_durability_desc	nvarchar(60)	The delayed durability setting: DISABLED ALLOWED FORCED Applies to: SQL Server 2014 (12.x) and later versions, and Azure SQL Database.
is_memory_optimized_elevate_to_snapshot_on	bit	Memory-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 isn't elevated.
is_federation_member	bit	Indicates if the database is a member of a federation. Applies to: Azure SQL Database
is_remote_data_archive_enabled	bit	Indicates whether the database is stretched. 0 = The database isn't Stretch-enabled. 1 = The database is Stretch-enabled. Applies to: SQL Server 2016 (13.x) and later versions For more information, see Stretch Database.
is_mixed_page_allocation_on	bit	Indicates 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. For more information, see the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE SET Options (Transact-SQL). Applies to: SQL Server 2016 (13.x) and later versions
is_temporal_history_retention_enabled	bit	Indicates whether temporal retention policy cleanup task is enabled. 1 = temporal retention is enabled 0 = temporal retention is disabled Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database
catalog_collation_type	int	The catalog collation setting: 0 = DATABASE_DEFAULT 2 = SQL_Latin_1_General_CP1_CI_AS Applies to: Azure SQL Database
catalog_collation_type_desc	nvarchar(60)	The catalog collation setting: DATABASE_DEFAULT SQL_Latin_1_General_CP1_CI_AS Applies to: Azure SQL Database
physical_database_name	nvarchar(128)	For SQL Server, the physical name of the database. For Azure SQL Database, a unique identifier for the current physical database corresponding to the user database. Changing the database service level objective or restoring the database will cause this value to change. Applies to: SQL Server 2019 (15.x) and later versions, and Azure SQL Database
is_result_set_caching_on	bit	Indicates whether result set caching is enabled. 1 = result set caching is enabled 0 = result set caching is disabled Applies to: Azure Synapse Analytics Gen2. While this feature is being rolled out to all regions, check the version deployed to your instance and the latest Azure Synapse release notes and Gen2 upgrade schedule for feature availability.
is_accelerated_database_recovery_on	bit	Indicates whether Accelerated Database Recovery (ADR) is enabled. 1 = ADR is enabled 0 = ADR is disabled Applies to: SQL Server 2019 (15.x) and later versions, and Azure SQL Database
is_tempdb_spill_to_remote_store	bit	Indicates whether tempdb spill to remote store is enabled. 1 = enabled 0 = disabled Applies to: Azure Synapse Analytics Gen2.
is_stale_page_detection_on	bit	Indicates whether stale page detection is enabled. 1 = stale page detection is enabled 0 = stale page detection is disabled Applies to: Azure Synapse Analytics Gen2. While this feature is being rolled out to all regions, check the version deployed to your instance and the latest Azure Synapse release notes and Gen2 upgrade schedule for feature availability.
is_memory_optimized_enabled	bit	Indicates whether certain In-Memory features, such as Hybrid buffer pool, are enabled for the database. Doesn't reflect the availability or configuration state of In-Memory OLTP overview and usage scenarios. 1 = memory-optimized features are enabled 0 = memory-optimized features are disabled Applies to: SQL Server 2019 (15.x) and later versions, and Azure SQL Database
is_data_retention_on	bit	Indicates whether data retention is enabled for an Azure Edge database. Applies to: Azure SQL Edge
is_ledger_on	bit	Indicates a ledger database, which is a database in which all user tables are ledger tables (all customer database is tamper-evident). Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database
is_change_feed_enabled	bit	Indicates whether the current database is enabled for Azure Synapse Link for SQL or Fabric Mirrored Database. Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database
is_vorder_enabled	bit	Indicates whether V-Order is enabled for each Warehouse. Applies to: Microsoft Fabric only.

Permissions

If the caller of sys.databases isn't the owner of the database and the database isn't master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or the 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.

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.

Remarks

In Azure 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.

Due to possible metadata synchronization, the sys.databases view might provide incorrect information regarding database encryption. To ensure accurate results, we recommend you use the sys.dm_database_encryption_keys view to obtain the actual encryption status.

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

Examples

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.percent_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;

C. Check the temporal retention policy status in SQL Database

The following example queries the sys.databases to return information whether temporal retention cleanup task is enabled. After the restore operation, temporal retention is disabled by default. Use ALTER DATABASE to enable it explicitly.

Applies to: Azure SQL Database

-- Execute from the master database.
SELECT a.name, a.is_temporal_history_retention_enabled
FROM sys.databases AS a;

Related content

• 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)