SQL Server Show/List Databases

Listing all databases in SQL Server is a common task for database administrators and developers. SQL Server provides two main methods to solve this such as using SQL commands and using SQL Server Management Studio (SSMS). In this article, we will learn about how to Show/List the SQL Server Databases using both approaches in detail.

SHOW/LIST Databases in SQL Server

Listing all databases in SQL Server is a common task for database administrators and developers. SQL Server provides two main methods to solve this task are defined below:

• Using SQL commands
  
• Using SQL Server Management Studio (SSMS)

SHOW Databases using SQL commands in the Command Prompt

We can use a command prompt in our system or SQL Server Management Studio (SSMS) to execute our SQL commands. Below are the commands or queries to show databases in the SQL Server system. To connect SQL Server in the command prompt of our system we use the below command.

C:\>sqlcmd -S <hostname>\<instancename>

Output:

1. Using SELECT command

The below command results in all the databases including user-databases available in the system:

SELECT name  FROM sys.databases;

Output:

Explanation: SELECT statement selects the NAME of the system databases including user-created databases using SYS.DATABASES command.

We can also display only required information of databases in the system, we use the query as below:

SELECT name, database_id, create_date FROM sys.databases;  

Output:

Explanation: The above query displays name, database_id, and create_date of system databases and user-created databases using SELECT and SYS.DATABASES command.

2. Using EXEC command

The other approach of listing all the databases is using EXEC command . Here the below command displays all the system and user databases.

EXEC sp_databases;

Output:

Explanation: The above query 'EXEC sp_databases' selects and displays database_name, database_size, and remarks of all the databases present in the SQL Server system.

How to get the User-Created Databases Name in SQL Server?

If we want to fetch only the names and details of user created databases, we need to execute the below query. This query filters the known system databases and displays user created ones.

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

Output:

Explanation: The above query displays name, database_id, create_date of user-created databases excluding system databases i.e,('master', 'model', 'msdb', 'tempdb').

Displaying user-created databases can also be done using the below query, where we use database_id to display databases other than 'master', 'model', 'msdb', 'tempdb'.

SELECT name, database_id FROM sys.databases 
WHERE database_id > 4;

Output:

Explanation: The above query selects the user-created databases based on database_id.

SHOW Databases Using SQL Server Management Studio (SSMS)

• Microsoft SQL Server Management Studio (SSMS) is a software application developed by Microsoft that is used for configuring, managing, and administering all components within Microsoft SQL Server.
  
• SSMS provides a single comprehensive utility that combines a broad group of graphical tools with many rich script editors to provide access to SQL Server for developers and database administrators of all skill levels.
  
• Therefore SSMS is a GUI tool which provides user-friendly interface to perform several operations using both SQL commands and in-built features.
  
• We can use to it display all the databases without any queries or SQL commands. Here are the steps to list databases present in the SQL Server instance.
  
• Follow the below steps to display the databases present in the SQL Server instance:

Step 1: Install the SQL Server Management Studio (SSMS) in your system. Refer to Installation Of SSMS.

Step 2: After installation open SSMS in administrator mode and grant all the permissions required.

Step 3: Now select 'Server type' as 'Database Engine' and connect to 'Server name' followed by your directory.

Step 4: Once the connection is established, we will see 'Object Explorer' in the top left corner. Now click '+' button of 'Databases' folder which will display all the databases present in the current server instance.

Conclusion

Overall, SQL Server offers a robust platform for managing databases, providing users with a range of tools and options for listing databases. Whether you prefer the flexibility of SQL commands or the user-friendly interface of SSMS, SQL Server caters to diverse user needs and environments. By following the techniques outlined in this article, you can efficiently navigate and manage databases in SQL Server.