How to List All Databases in SQL Server

To get a list of all databases in the SQL server use the command:

SELECT * FROM sys.databases;

This command can also be modified to only look at system databases or user databases. Let’s look at the commands for each requirement.

List All System databases in SQL Server

The commands to see system databases are :

SELECT name, database_id, create_date  
FROM sys.databases ;  

Output:

There are mainly four types of system databases :

1. master
2. model
3. msdb
4. tmpdb

Some other databases are also present in the server other than the above ones. Those can be displayed as shown below:

SELECT name FROM master.dbo.sysdatabases

Output:

List All User-Defined Databases in SQL Server

Now in order to select the user-defined the first let’s create some databases in the server.

We will be using the below-mentioned commands to add some databases to the SQL server:

CREATE DATABASE GFG;
CREATE DATABASE GFG1;
CREATE DATABASE GFG2;

Output:

The command to list all user-defined databases present in the server:

SELECT name  
FROM sys.Databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); 

Output:

Hence in this way we are able to select and list all the user-defined and system databases in the SQL server.

The list of all databases in SQL Server is present in the sys.databases view. Using SQL SELECT statement on sys.databases view, users can fetch a list of all databases and their information.

The sys.database view contains details about all databases, like- name, database_id, create_date, compatibility_level, state_desc, the current state of the database (ONLINE, OFFLINE, RESTORING, etc.),recovery_model_desc (FULL, BULK_LOGGED, SIMPLE), is_read_only, and user_access_desc.

There are two types of databases in SQL Server:

• System Databases: They are installed during the SQL Server installation and are used to manage and maintain the SQL Server instance.
• User-Defined Databases: They are created and managed by users to store application data.