SQL Server Metadata / System Views – sys.databases

Querying directly from SQL Server system view is very helpful for intermediate to advanced users. One of such table is sys.databases which contains one row per database in the instance of Microsoft SQL Server. This view is very useful to know the details about any database on the server and also to be used as a part of complex custom query for advanced opearation.

Some of the information I often find useful from this table are as below:

Name – Database name

Database ID – SQL Server generated unique id of the database

Source Database ID – It is Null if database is not a snapshot. Otherwise it will present Database ID of the original database from where this snapshot is created.

Create Date - Date the database was created or renamed. (So this value for tempdb essentially represents when SQL Server was started last time)

compatibility_level – Compatibility level of database. (NULL if database is offline or Auto_Close is on)

collation_name – Default collation of the database. (NULL if database is offline or Auto_Close is on)

user_access_desc – User access mode

is_read_only – Read only status of the database ( 1 = read only, 0 = read_write allowed)

is_auto_shrink_on -  Auto shrink status of the database ( 1 = on, 0 = off)

state_desc – State of the database (eg. Online, Suspect, Restoring etc)

is_cleanly_shutdown – If the database will require a recovery at startup (1 = No, 0 = Yes)

snapshot_isolation_state_desc - Snapshot isolation level state

is_read_committed_snapshot_on – READ_COMMITED_SNAPSHOT status (1 = On, 0 = Off)

recovery_model_desc – Recovery model of the database

page_verify_option_desc – Page verification setting of the database

is_auto_create_stats_on – Auto create stat status (1 = On, 0 = Off)

is_auto_update_stats_on - Auto update stat status (1 = On, 0 = Off)

is_auto_update_stats_async_on - AUTO_UPDATE_STATISTICS_ASYN status (1 = On, 0 = Off)

is_fulltext_enabled – Full text status (1 = On, 0 = Off)

is_trustworthy_on – Trustworthy status (1 = On, 0 = Off)

is_master_key_encrypted_by_server – If database has an encrypted master key (1 = Yes, 0 = No)

is_published – Is database a publisher in transactional or snapshot replication (1 = Yes, 0 = No)

is_subscribed – Is database a subscriber in a replication topology (1 = Yes, 0 = No)

is_merge_published - Is database a publisher in merge replication (1 = Yes, 0 = No)

is_distributor - Is database a distributor in replication (1 = Yes, 0 = No)

is_sync_with_backup – Is database is marked for replication synchronization with backup. (1 = Yes, 0 = No)

is_broker_enabled – Service broker status in the database ( 1 = Enabled, 0 = Disabled. All message are queued and receiving is not enabled)

log_reuse_wait_desc – Why transaction log reuse is waiting

is_cdc_enabled – Is CDC is enabled (1 = enabled)

is_encrypted – Is the database encrypted? (1 = Yes, 0 = No)

Unless you have a server level privilege at least as ALTER ANY DATABASE or VIEW ANY DATABASE or CREATE DATABASE permission in the master database, you will not be able to see the information for all databases. The database to which the caller is connected can always be viewed in sys.databases.

For a detailed description of all the information related to sys.databases, please refer to http://msdn.microsoft.com/en-us/library/ms178534(v=SQL.100).aspx