How to Display All Databases in Various Ways

Viewing a complete list of databases on a server, accessing their relevant information, and identifying specific databases quickly are essential tasks in daily database management. These operations are vital for executing cross-database queries, integrating databases, monitoring sizes and performance, conducting security audits, and many other aspects of database development, management, and administration.

Modern database management systems, including Microsoft SQL Server, offer a variety of methods for efficiently displaying existing databases. SQL Server, for instance, offers multiple ways to list databases, similar to how one would view tables within a database. These methods include using dedicated T-SQL queries, command-line tools, and graphical user interface (GUI) solutions. This article will explore these techniques in detail.

Use dedicated T-SQL commands with CLI

The standard method for interacting with SQL Server involves the sqlcmd utility. This command-line tool enables users to execute T-SQL statements, run scripts, and automate various tasks. In this guide, we'll use sqlcmd to demonstrate how to view lists of databases.

In SQL Server, you can list all the databases on a server instance by querying the sys.databases view or using the sp_databases or sp_helpdb stored procedures. Let's explore both approaches and their respective commands.

Query sys.databases to list databases

The sys.databases view holds a list of all databases, along with additional related information. To retrieve the list of databases only, use the SQL SELECT command:

SELECT name 
FROM sys.databases;

This command displays all databases, including both system and user-defined ones.

Note

Before querying sys.databases, ensure you have the necessary permissions. If you are not the database owner, or if the database is neither master nor tempdb, you will require either the VIEW ANY DATABASE server-level permission or the CREATE DATABASE permission in the master database.

To tailor the output to specific needs, you can modify the command. For example, the following command retrieves only the database IDs and their creation dates:

SELECT name, database_id, create_date 
FROM sys.databases;

View the list with additional database details

This approach offers a clear, concise way to manage and view database information using SQL commands.

Query stored procedures to list databases

In addition to querying the sys.databases view, you can obtain information about databases through the sp_databases or sp_helpdb stored procedures.

The sp_databases stored procedure retrieves basic information, including the database name and size. To execute this procedure, use the following command:

EXEC sp_databases;

Refer to the sp_databases stored procedure

The sp_helpdb stored procedure provides an extensive set of data about each database. We will examine the results it brings further when exploring the usage of GUI tools for listing databases and retrieving the related details. Both procedures are helpful when you need to access different levels of detail about the databases in your SQL Server environment.

Use GUI-based tools

Graphical User Interfaces (GUIs) have transformed our interactions with technology. GUI tools for databases simplify complex tasks, making them accessible to users of all skill levels. They allow for enhancing productivity and reducing errors due to converting many tasks into a visual format.

Popular integrated development environments (IDEs) for SQL Server offer comprehensive functionality, covering both routine jobs and complicated tasks. Viewing databases on the server is one of many routines that can be quickly and easily accomplished with GUI tools. Additionally, visual interfaces provide a more user-friendly way to access information, especially when dealing with large query outputs containing numerous details.

Now, we'll demonstrate the advantages of GUI tools using SQL Server Management Studio and dbForge Studio for SQL Server as examples. Below you can see the output brought by the sp_helpdb stored procedure mentioned earlier. The GUI of dbForge Studio for SQL Server presents the information in a better-organized manner.

Refer to the sp_helpdb stored procedure for more details in GUI

Show all SQL Server databases in SSMS

SQL Server Management Studio (SSMS) is the default integrated development environment for SQL Server databases. Developed and supported by Microsoft, SSMS is always compatible with the latest SQL Server innovations. As a graphical user interface (GUI)-based tool, SSMS allows users to perform many tasks visually.

Listing databases on a particular SQL Server instance is a standard task that can be done in SSMS both through the T-SQL commands and visually.

First, you can execute the standard commands to query sys.databases or sp_databases as was demonstrated earlier.

SELECT * 
FROM sys.databases;

View the SELECT statement output in a GUI

Another option is viewing the list of databases in Object Explorer on the left side of the SSMS GUI. This panel displays all the databases on the server, including both system and user-defined databases.

For detailed information about a database, right-click the database in the Object Explorer panel and select Properties to view the data related to the specific database.

View additional information about the specific database

List all databases with dbForge Studio for SQL Server

dbForge Studio for SQL Server is a much more powerful alternative to SSMS, offering enhanced functionality and an improved GUI, thus allowing users to access necessary information faster and perform database tasks more efficiently. The interface of dbForge Studio resembles SSMS, making it easy for users to transition from SSMS.

In dbForge Studio for SQL Server, users can write T-SQL queries in the SQL Editor and execute them immediately against the selected database:

dbForge Studio for SQL Server also features its Database Explorer on the left side, displaying a list of databases for each SQL Server instance.

View the database list in dbForge Studio Database Explorer

To view detailed information for any database, right-click the database name and select Properties. This displays the details in the same window.

View the database properties in dbForge Studio

In addition to Object Explorer, dbForge Studio provides a quicker way to list all databases: the Object Viewer feature.

Select the connection to a specific SQL Server instance, go to View > Object Viewer, or press F7. The Studio will then display a list of all databases with additional details, eliminating the need to execute commands manually or scroll through database lists in Object Explorer.

You can try dbForge Studio for SQL Server with a fully functional 30-day free trial. Download, install, and explore its capabilities under your regular SQL Server database workload.

Further Learning

To effectively master dbForge Studio for SQL Server and leverage its capabilities for your database tasks, please refer to the following learning resources:

Conclusion

Listing all databases on an SQL Server instance is a common and routine task for database specialists, often performed multiple times during a typical workday. Speed and simplicity are crucial for this task. SQL Server provides two primary methods for this: T-SQL commands and graphical user interface tools like SSMS. However, Devart offers a robust alternative with dbForge Studio for SQL Server. This tool enhances productivity by allowing users to interact with T-SQL code and execute various tasks visually, often outperforming SSMS in speed and convenience. You can download dbForge Studio for SQL Server to integrate it into your daily workflow and see the benefits for yourself.