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.

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.