|
Column name
|
Data type
|
Description
|
|
name
|
sysname
|
Name of database, unique within an instance of SQL Server.
|
|
database_id
|
int
|
ID of the database, unique within an instance of SQL Server.
|
|
source_database_id
|
int
|
Non-NULL = ID of the source database of this database snapshot.
NULL = Not a database snapshot.
|
|
owner_sid
|
varbinary(85)
|
SID (Security-Identifier) of the external owner of the database, as registered to the server.
|
|
create_date
|
datetime
|
Date the database was created or renamed. For tempdb, this value changes every time the server restarts.
|
|
compatibility_level
|
tinyint
|
Integer corresponding to the version of SQL Server for which behavior is compatible:
90
100
110
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
.gif)
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.
|
|
|
state_desc
|
nvarchar(60)
|
Description of the database state:
-
ONLINE
-
RESTORING
-
RECOVERING
-
RECOVERY_PENDING
-
SUSPECT
-
EMERGENCY
-
OFFLINE
|
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.
|
|
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:
|
|
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.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)
|
|
log_reuse_wait_desc
|
nvarchar(60)
|
Description of reuse of transaction log space is currently waiting on one of the following:
|
|
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.
Does not apply to SQL Azure.
|
|
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.
Does not apply to SQL Azure.
|
|
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.
|
|
default_language_name
|
nvarchar(128)
|
Indicates the default language of a contained database.
This value is null for a non-contained database.
Does not apply to SQL Azure.
|
|
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.
Does not apply to SQL Azure.
|
|
default_fulltext_language_name
|
nvarchar(128)
|
Indicates the default fulltext language of the contained database.
This value is null for a non-contained database.
Does not apply to SQL Azure.
|
|
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.
Does not apply to SQL Azure.
|
|
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.
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.
Does not apply to SQL Azure.
|
|
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. See sys.configurations (Transact-SQL) for further information.
Does not apply to SQL Azure.
|
|
containment
|
tinyint not null
|
Indicates the containment status of the database.
0 = database containment is off
1 = database is in partial containment
2 = database is in full containment
Does not apply to SQL Azure.
|
|
containment_desc
|
nvarchar(60) not null
|
Indicates the containment status of the database.
NONE = legacy database (zero containment)
PARTIAL = partially contained database
FULL = fully contained database
Does not apply to SQL Azure.
|
|
target_recovery_time_in_seconds
|
int
|
The estimated time to recover the database, in seconds. Nullable.
|