SQL Server Show/List Databases

It's a common task to display or list all databases, listing tables of a particular database, and information on user accounts and their privileges on the server while working with the SQL server. This topic will give a complete explanation of how to view a list of databases on an instance of SQL Server.

SQL Server categorizes the database into two categories:

System Databases: When we install MS SQL Server on our system, the system databases are created automatically. It plays an important role for the server to function properly. The following is a list of SQL Server's system databases: Master, Model, MSDB, Tempdb, etc.

User Databases: These databases are created by database users like DBAs and testers who can also access and manipulate their data.

SQL Server provides two ways to list all or specific databases:

• Transact-SQL Command
• SQL Server Management Studio (SSMS)

Let us discuss them one by one in detail.

SHOW databases using SQL Command

We can use the below SQL statement to displays a list of databases on the SQL Server instance. This statement returns the output containing the names of the databases, their IDs, and the dates on which they were created. It should be noted that this command will display all databases that are system and user-defined databases both.

This command will display the detailed information of all databases:

If we want to show only useful information in displaying the list of databases, we use the query as below:

Executing the command will return the below output:

SQL Server also provides another method to display the list of all databases in SQL Server instances. This method uses the stored procedure statement as below:

This stored procedure returns the below output containing the database name and their size:

How to get the user-created databases name in SQL Server?

If we want to get only the names of user-created databases present in the SQL Server instance, we need to write the query below. This query will filter the results to remove the known system databases as we know that the SQL Server does not provide any mechanism for identifying a database as either a system or a user-created database.

Executing the statement will display only the user-defined databases:

SHOW databases using SQL Server Management Studio (SSMS)

SSMS is a GUI tool to perform several operations efficiently. We can also use it to display the list of databases present in the SQL Server instance. The following steps are used to list the databases in SSMS:

Step 1: To avoid any permission issues, start SSMS in administrator mode. We will see the screen below, where we'll need to click the Connect button to continue.

Step 2: The Object Explorer window will display on the left-hand side of the screen once the connection is established. If the Database folder isn't visible, click the "+" button to expand it.

Step 3: Click the plus(+) icon to expand the Databases folder. It will display all databases (system and user) present in the current server instance.