SQL Query to List All Databases

SQL language is a DML in DBMS. This is used to manipulate databases and the records kept in them. A database is a collection of structured information or data stored in any computer system. So to modify these databases or to update any data contained by them, we are using DML languages like SQL, etc. We will mainly be using MS SQL SERVER for this topic.

So first let us create some of the databases which we will be using in this article to query up using SQL.

To create a database in SQL we have to use the following command:

CREATE DATABASE database_name;

This command creates a database of the given names in the SQL server and then we can add tables to this database also through SQL. We can also add tuples or rows in these tables, or we can delete the tables or update or modify them according to our needs using SQL. 

To use any particular database we should select between them which we can do as shown below:

USE database_name;

So let’s add two tables to this database using SQL.

To do that we will be using the below given commands:

CREATE TABLE [database_name.]table_name (
   pk_column data_type PRIMARY KEY,
   column_1 data_type NOT NULL,
   column_2 data_type,
   ...,
   table_constraints
);

If we do not mention the name of the database then the default USE database is selected for the creation of tables.

Below are some commands which shows how we created databases and then how we listed them and run queries on them.

There are default databases present on SQL server initially, which are of two types :

1. System databases:

The command 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:

2. User-Defined Databases:

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

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

create database GFG;
create database GFG1;
create database GFG2;

Output:

These are the query to list the user-defined database present in the server (while we had done above):

select name  
from sys.Databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');  /* removing the name of sys db*/

Output:

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