sys.databases (Transact-SQL)

Returns a row for each database in an instance of SQL Server.

Description

The sys.databases catalog view contains a row for each database in an instance of SQL Server. It exposes information about the database, such as its name, ID, creation date, and state.

Permissions

In SQL Server, users typically need VIEW ANY DATABASE permission or higher to query sys.databases. For specific databases, permissions like VIEW DEFINITION on the database might be required.

Columns

Name Data Type Description
database_id int The unique identifier of the database.
name sysname The name of the database.
owner_sid varbinary(85) The security identifier (SID) of the database owner.
create_date datetime The date and time the database was created.
compatibility_level tinyint The compatibility level of the database.
collation_name nvarchar(128) The collation name of the database.
user_access tinyint The current user access mode of the database. 0 = Multi_user, 1 = Single_user, 2 = Restricted_user.
state tinyint The current state of the database. 0 = ONLINE, 1 = RESTORING, 2 = RECOVERING, 3 = RECOVERY_PENDING, 4 = SUSPECT, 5 = EMERGENCY, 6 = OFFLINE, 7 = COPY_ONLY_STANDBY.
is_read_only bit 1 indicates the database is read-only.
containment tinyint The type of containment. 0 = NONE, 1 = PARTIAL.

Example

The following query retrieves the name, ID, creation date, and current state of all databases on the SQL Server instance:

SELECT
    name,
    database_id,
    create_date,
    state_desc
FROM
    sys.databases;

Note: The state_desc column provides a human-readable description of the database state.

Example with filtering

This example shows how to retrieve information for databases that are currently online:

SELECT
    name,
    database_id,
    create_date
FROM
    sys.databases
WHERE
    state = 0; -- 0 indicates ONLINE state

Additional Information

sys.databases is a fundamental catalog view for understanding and managing databases within SQL Server. It provides essential metadata that can be used for scripting, monitoring, and troubleshooting database operations.

For more detailed information about specific databases, you might need to join this view with other catalog views such as sys.master_files or sys.database_files.

Related Topics