Export (0) Print
Expand All
6 out of 11 rated this helpful - Rate this topic

sys.databases (Transact-SQL)

Contains one row per database in the instance of Microsoft SQL Server or the Windows Azure SQL Database server.

Applies to: SQL Server (SQL Server 2008 through current version), Windows Azure SQL Database (Initial release through current release).

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.

Column name

Data type

Description

name

sysname

Name of database, unique within an instance of SQL Server or within a Windows Azure SQL Database server.

database_id

int

ID of the database, unique within an instance of SQL Server or within a Windows Azure SQL Database 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:

Value

Applies to

70

SQL Server 2008 and SQL Server 2008 R2

80

SQL Server 2008 and SQL Server 2008 R2

90

SQL Server 2008 through SQL Server 2012 and Windows Azure SQL Database

100

SQL Server 2008 through SQL Server 2012 and Windows Azure SQL Database

110

SQL Server 2012

NULL

SQL Server 2008 through SQL Server 2012 and Windows Azure SQL Database

NULL = Database is not online, or AUTO_CLOSE is set to ON and the database is closed.

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 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:

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

7 = COPYING (Applies to Windows Azure SQL Database)

Windows Azure SQL Database returns the states 0, 1, 4, and 7.

Note 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 AlwaysOn databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.

For more information, see Database States.

state_desc

nvarchar(60)

Description of the database state:

  • ONLINE

  • RESTORING

  • RECOVERING

  • RECOVERY_PENDING

  • SUSPECT

    For SQL Database, SUSPECT = copy process has failed.

  • EMERGENCY

  • OFFLINE

  • COPYING

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 did not 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. 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

This column is not 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 = 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 = An AlwaysOn 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 2012. In earlier versions of SQL Server, 9 = Other (Transient).

10 = For internal use only

11 = For internal use only

12 = For internal use only

13 = Oldest page

Applies to: SQL Server 2012 through SQL Server 2012.

14 = Other (transient)

Applies to: SQL Server 2012 through SQL Server 2012.

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

    Note 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

  • AVAILABILITY_REPLICA

    Note Note

    To identify which secondary database is delaying log truncation, see the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view.

  • For internal use only

  • For internal use only

  • For internal use only

  • OLDEST_PAGE

  • OTHER_TRANSIENT

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

replica_id

uniqueidentifier

Unique identifier of the local AlwaysOn 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 2012 and SQL Database.

group_database_id

uniqueidentifier

Unique identifier of the database within an AlwaysOn 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 2012 and SQL Database.

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 through SQL Server 2012 and SQL Database.

default_language_name

nvarchar(128)

Indicates the default language of a contained database.

This value is null for a non-contained database.

default_fulltext_language_lcid

int

Indicates 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 2012 and 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 through SQL Server 2012 and SQL Database.

is_nested_triggers_on

bit

Indicates 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 2012 and SQL Database.

is_transform_noise_words_on

bit

Indicates whether or noise words should be transformed in the contained database.

0 = noise words should not be transformed.

1 = noise words should be transformed.

Applies to: SQL Server 2012 through SQL Server 2012.

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.

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.

Applies to: SQL Server 2012 through SQL Server 2012 and SQL Database.

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.

containment

tinyint not null

Indicates the containment status of the database.

0 = database containment is off

1 = database is in partial containment

Applies to: SQL Server 2012 through SQL Server 2012 and SQL Database.

containment_desc

nvarchar(60) not null

Indicates the containment status of the database.

NONE = legacy database (zero containment)

PARTIAL = partially contained database

Applies to: SQL Server 2012 through SQL Server 2012 and SQL Database.

target_recovery_time_in_seconds

int

The estimated time to recover the database, in seconds. Nullable.

Applies to: SQL Server 2012 through SQL Server 2012.

is_federation_member

bit

Indicates if the database is a member of a federation.

Applies to: Windows Azure SQL Database

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.

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

Use the sys.databases view in the master database of the Windows Azure 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.

Applies to: SQL Server 2008 through SQL Server 2012 and Windows Azure SQL Database

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

B. Check the copying status in Windows 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: Windows 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;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.