---
title: "sys.databases (Transact-SQL)"
description: sys.databases (Transact-SQL)
author: rwestMSFT
ms.author: randolphwest
ms.date: 08/20/2025
ms.service: sql
ms.subservice: system-objects
ms.topic: "reference"
ms.custom:
- ignite-2024
- build-2025
f1_keywords:
- "databases"
- "databases_TSQL"
- "sys.databases"
- "sys.databases_TSQL"
helpviewer_keywords:
- "sys.databases catalog view"
dev_langs:
- "TSQL"
monikerRange: ">=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric"
---
# sys.databases (Transact-SQL)
[!INCLUDE [sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb.md)]
Contains one row per database in the instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)].
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 [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] or within a [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] logical server. |
| `database_id` | **int** | ID of the database, unique within an instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)].
In [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)], the values are unique within a logical server, and are joinable with [sys.database_service_objectives](sys-database-service-objectives-azure-sql-database.md) on the `database_id` column, but not with other system views where the `database_id` column is present. For details, see [DB_ID](../../t-sql/functions/db-id-transact-sql.md#remarks). |
| `source_database_id` | **int** | Non-NULL = ID of the source database of this database snapshot.
NULL = Not a database snapshot. |
| `owner_sid` | **varbinary(85)** | Security identifier (SID) 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](../../t-sql/statements/alter-authorization-transact-sql.md#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` doesn't change when a rename operation is performed on Azure SQL Database, Warehouse and SQL analytics endpoint in Microsoft Fabric |
| `compatibility_level` 1 | **tinyint** | Integer corresponding to the version of the [!INCLUDE [ssde-md](../../includes/ssde-md.md)] for which behavior is compatible. Valid values are `80`, `90`, `100`, `110`, `120`, `130`, `140`, `150`, `160`, and `170`. |
| `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](../system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql.md).
1 **Applies to**: [!INCLUDE [sql2008-md](../../includes/sql2008-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]
2 **Applies to**: [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] [!INCLUDE [ssGeoDR](../../includes/ssgeodr-md.md)] |
| `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**: [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] 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](sys-database-query-store-options-transact-sql.md) to view the Query Store status.
`0` = The Query Store isn't enabled
**Applies to**: [!INCLUDE [sssql16-md](../../includes/sssql16-md.md)] 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](../logs/the-transaction-log-sql-server.md).
**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](../accelerated-database-recovery-concepts.md#adr-recovery-components) is used 5
1 **Applies to**: [!INCLUDE [sql2008-md](../../includes/sql2008-md.md)] and later versions
2 **Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions
3 **Applies to**: [!INCLUDE [sql2008r2-md](../../includes/sql2008r2-md.md)] and earlier versions
4 **Applies to**: [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] and later versions
5 **Applies to**: [!INCLUDE [sql-server-2019](../../includes/sssql19-md.md)] 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](../system-stored-procedures/sys-sp-cdc-enable-db-transact-sql.md). |
| `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)](../security/encryption/transparent-data-encryption.md).
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](../system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql.md) 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 [!INCLUDE [ssHADR](../../includes/sshadr-md.md)] 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**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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**: [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] and later versions |
| `default_language_lcid` | **smallint** | Indicates the local ID (`lcid`) of the default language of a contained database.
**Note:** Functions as the [default language](../../database-engine/configure-windows/configure-the-default-language-server-configuration-option.md) server configuration value. This value is `NULL` for a non-contained database.
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `default_language_name` | **nvarchar(128)** | Indicates the default language of a contained database.
This value is `NULL` for a non-contained database.
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `default_fulltext_language_lcid` | **int** | Indicates the locale ID (lcid) of the default fulltext language of the contained database.
**Note:** Functions as the default [default full-text language](../../database-engine/configure-windows/configure-the-default-full-text-language-server-configuration-option.md) server configuration value. This value is `NULL` for a non-contained database.
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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 [nested triggers](../../database-engine/configure-windows/configure-the-nested-triggers-server-configuration-option.md) server configuration value. This value is `NULL` for a non-contained database. For more information, see [sys.configurations](sys-configurations-transact-sql.md).
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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](../../database-engine/configure-windows/transform-noise-words-server-configuration-option.md) server configuration value. This value is `NULL` for a non-contained database. For more information, see [sys.configurations](sys-configurations-transact-sql.md).
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] 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 [two digit year cutoff](../../database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option.md) server configuration value. This value is `NULL` for a non-contained database. For more information, see [sys.configurations](sys-configurations-transact-sql.md).
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `containment` | **tinyint** | Indicates the containment status of the database. Not nullable.
`0` = database containment is off. **Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)].
`1` = database is in partial containment **Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] 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**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `target_recovery_time_in_seconds` | **int** | The estimated time to recover the database, in seconds. Nullable.
**Applies to**: [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `delayed_durability` | **int** | The delayed durability setting:
`0` = `DISABLED`
`1` = ALLOWED
`2` = `FORCED`
For more information, see [Control Transaction Durability](../logs/control-transaction-durability.md).
**Applies to**: [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]. |
| `delayed_durability_desc` | **nvarchar(60)** | The delayed durability setting:
`DISABLED`
`ALLOWED`
`FORCED`
**Applies to**: [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]. |
| `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**: [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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**: [!INCLUDE [sssql16-md](../../includes/sssql16-md.md)] and later versions
For more information, see [Stretch Database](/previous-versions/sql/sql-server/stretch-database/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](../../t-sql/statements/alter-database-transact-sql-set-options.md).
**Applies to**: [!INCLUDE [sssql16-md](../../includes/sssql16-md.md)] 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**: [!INCLUDE [ssSQL17](../../includes/sssql17-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `catalog_collation_type` | **int** | The catalog collation setting:
`0` = DATABASE_DEFAULT
`2` = `SQL_Latin_1_General_CP1_CI_AS`
**Applies to**: [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `catalog_collation_type_desc` | **nvarchar(60)** | The catalog collation setting:
DATABASE_DEFAULT
`SQL_Latin_1_General_CP1_CI_AS`
**Applies to**: [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `physical_database_name` | **nvarchar(128)** | For [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)], the physical name of the database. For [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)], 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**: [!INCLUDE [sql-server-2019](../../includes/sssql19-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `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**: [!INCLUDE [ssazuresynapse-md](../../includes/ssazuresynapse-md.md)] dedicated SQL pools and [!INCLUDE [fabric](../../includes/fabric.md)]. For more information, see [Result set caching (preview)](/fabric/data-warehouse/result-set-caching). |
| `is_accelerated_database_recovery_on` | **bit** | Indicates whether Accelerated Database Recovery (ADR) is enabled.
`1` = ADR is enabled
`0` = ADR is disabled
**Applies to**: [!INCLUDE [sql-server-2019](../../includes/sssql19-md.md)] and later versions, [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)], and [!INCLUDE [ssazuremi-md](../../includes/ssazuremi-md.md)] |
| `is_tempdb_spill_to_remote_store` | **bit** | Indicates whether `tempdb` spill to remote store is enabled.
`1` = enabled
`0` = disabled
**Applies to**: [!INCLUDE [ssazuresynapse-md](../../includes/ssazuresynapse-md.md)] 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**: [!INCLUDE [ssazuresynapse-md](../../includes/ssazuresynapse-md.md)] 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](/azure/synapse-analytics/sql-data-warehouse/release-notes-10-0-10106-0) and [Gen2 upgrade schedule](/azure/synapse-analytics/sql-data-warehouse/gen2-migration-schedule) for feature availability. |
| `is_memory_optimized_enabled` | **bit** | Indicates whether certain In-Memory features, such as [Hybrid buffer pool](../../database-engine/configure-windows/hybrid-buffer-pool.md), are enabled for the database. Doesn't reflect the availability or configuration state of [In-Memory OLTP overview and usage scenarios](../in-memory-oltp/overview-and-usage-scenarios.md).
`1` = memory-optimized features are enabled
`0` = memory-optimized features are disabled
**Applies to**: [!INCLUDE [sql-server-2019](../../includes/sssql19-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `is_data_retention_on` | **bit** | Indicates whether [data retention](/azure/azure-sql-edge/data-retention-overview) is [enabled for an Azure Edge database](/azure/azure-sql-edge/data-retention-enable-disable#check-if-data-retention-is-enabled-for-a-database).
**Applies to**: [!INCLUDE [ssazure-sqldb](../../includes/ssazurede-md.md)] |
| `is_ledger_on` | **bit** | Indicates a [ledger database](/azure/azure-sql/database/ledger-overview#ledger-database), which is a database in which all user tables are ledger tables (all customer database is tamper-evident).
**Applies to**: [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] |
| `is_change_feed_enabled` | **bit** | Indicates whether the current database is enabled for [Azure Synapse Link for SQL](/azure/synapse-analytics/synapse-link/sql-synapse-link-overview), change event streaming, or [Fabric Mirroring](/fabric/database/mirrored-database/overview).
**Applies to**: [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] and later versions, and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]. |
| `is_data_lake_replication_enabled` | **bit** | Indicates whether the current database is enabled for [Fabric Mirroring](/fabric/database/mirrored-database/overview).
**Applies to**: [!INCLUDE [sssql25-md](../../includes/sssql25-md.md)] and later versions, [!INCLUDE [ssazuremi-md.md](../../includes/ssazuremi-md.md)], and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]. |
| `is_event_stream_enabled` | **bit** | Indicates whether the current database has [Change event streaming (preview)](../track-changes/change-event-streaming/overview.md) enabled.
**Applies to**: [!INCLUDE [sssql25-md](../../includes/sssql25-md.md)] and later versions. |
| `is_vorder_enabled` | **bit** | Indicates whether [V-Order](/fabric/data-warehouse/v-order) is enabled for each [!INCLUDE [fabric-dw](../../includes/fabric-dw.md)].
**Applies to**: [!INCLUDE [fabric](../../includes/fabric.md)] only.|
| `is_optimized_locking_on` | **bit** | Indicates whether optimized locking is enabled.
`1` = Optimized locking is enabled
`0` = Optimized locking is disabled
**Applies to**: [!INCLUDE [sssql25-md](../../includes/sssql25-md.md)] and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]. |
1 For a full list of compatibility levels, see [ALTER DATABASE compatibility level](../../t-sql/statements/alter-database-transact-sql-compatibility-level.md)
## 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 [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] 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](../system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql.md) view to obtain the actual encryption status.
Use the `sys.databases` view in the `master` database of the [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)] 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.
```sql
SELECT name,
user_access_desc,
is_read_only,
state_desc,
recovery_model_desc
FROM sys.databases;
```
### B. Check the copying status in Azure 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**: [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]
```sql
-- 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**: [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)]
```sql
-- 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)](../../t-sql/statements/alter-database-transact-sql.md)
- [ALTER DATABASE (Transact-SQL) compatibility level](../../t-sql/statements/alter-database-transact-sql-compatibility-level.md)
- [sys.database_mirroring_witnesses (Transact-SQL)](database-mirroring-witness-catalog-views-sys-database-mirroring-witnesses.md)
- [sys.database_recovery_status (Transact-SQL)](sys-database-recovery-status-transact-sql.md)
- [Databases and Files Catalog Views (Transact-SQL)](databases-and-files-catalog-views-transact-sql.md)
- [sys.dm_database_copies (Azure SQL Database)](../system-dynamic-management-views/sys-dm-database-copies-azure-sql-database.md)