MySQL - SHOW Databases

Table of content

MySQL SHOW Databases Statement

To display the list of all databases present in a MySQL server, we need to use the SHOW DATABASES statement. It returns the result in a tabular form with one column. The databases in the result set will be sorted in alphabetical order.

Syntax

Following is the syntax to list all databases in MySQL sever −

SHOW DATABASES [LIKE 'pattern' | WHERE expr]

Example

First of all, let us create a database with a name TUTORIALS using the following query −

CREATE DATABASE TUTORIALS;

Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases by using the following query −

SHOW DATABASES;

Output

The above query displayed all the databases present in the current MySQL server.

Database
information_schema
mysql
performance_schema
tutorials

MySQL SHOW SCHEMAS Statement

We can also use the SHOW SCHEMAS statement to list out the databases in MySQL.

Syntax

Following is the syntax of the MySQL SHOW SCHEMAS statement −

SHOW SCHEMAS [LIKE 'pattern' | WHERE expr]

Example

Lets try to verify the list of databases once again you can using the following query −

SHOW SCHEMAS;

Output

The output of the above query will be as shown below −

Database
information_schema
mysql
performance_schema
tutorials

Showing Databases Using a Client Program

Besides fetching the list of all databases in current MySQL server with a MySQL query, we can also use a client program to perform the SHOW DATABASES operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

PHP NodeJS Java Python

To show the list of all databases present in current MySQL through PHP program, we need to execute the SHOW DATABASES statement using the mysqli function query() as follows −

$sql = "SHOW Database_name";
$mysqli->query($sql);

To show the list of all databases present in current MySQL through Node.js program, we need to execute the SHOW DATABASES statement using the query() function of the mysql2 library as follows −

sql= "SHOW {DATABASES | SCHEMAS} LIKE 'pattern' | WHERE expr]";
con.query(sql);

To show the list of all databases present in current MySQL through Java program, we need to execute the SHOW DATABASES statement using the JDBC function executeUpdate() as follows −

String sql = "SHOW Database_name";
st.executeQuery(sql);

To show the list of all databases present in current MySQL through Python program, we need to execute the SHOW DATABASES statement using the execute() function of the MySQL Connector/Python as follows −

sql = "SHOW Database_name";
cursorObj.execute(sql)

Example

Following are the programs −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
if ($result = $mysqli->query("SHOW DATABASES")) {
   printf("Show Database executed successfully..!");
   echo "Database list are: ";
   while($row = mysqli_fetch_array($result)){
       print_r($row);
   }
}
if ($mysqli->errno) {
    printf("Could not find database: %s
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Show Database executed successfully..!Database list are:
Array
(
    [0] => bank
    [Database] => bank
)
Array
(
    [0] => company
    [Database] => company
)
Array
(
    [0] => information_schema
    [Database] => information_schema
)
Array
(
    [0] => mydb
    [Database] => mydb
)
Array
(
    [0] => mysql
    [Database] => mysql
)
Array
(
    [0] => performance_schema
    [Database] => performance_schema
)
Array
(
    [0] => sys
    [Database] => sys
)
Array
(
    [0] => testdb
    [Database] => testdb
)
Array
(
    [0] => tutorials
    [Database] => tutorials
)
Array
(
    [0] => usersdb
    [Database] => usersdb
)

var mysql = require('mysql2');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

//Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
console.log("--------------------------");

//Creating a Database
sql = "create database testDB1"
con.query(sql);
sql = "create database testDB2"
con.query(sql);
sql = "create database testDB3"
con.query(sql);
sql = "create database testDB4"
con.query(sql);

//Displaying Databases
sql = "show databases;"
con.query(sql, function(err, result){
  if (err) throw err
  console.log(result)
});
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { Database: 'customers' },
  { Database: 'information_schema' },
  { Database: 'mysql' },
  { Database: 'performance_schema' },
  { Database: 'testdb1' },
  { Database: 'testdb2' },
  { Database: 'testdb3' },
  { Database: 'testdb4' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowDatabase {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st1 = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SHOW DATABASES";
            rs = st1.executeQuery(sql);
            System.out.println("Show query executed successfully...!");
            System.out.println("Databases are: ");
            while(rs.next()) {
            	String db = rs.getNString(1);
            	System.out.println(db);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Output

The output obtained is as shown below −

Show query executed successfully...!
Databases are: 
bank
company
information_schema
mydb
mysql
performance_schema
sys
testdb
tutorials
usersdb

import mysql.connector
# Creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# Creating cursor object
cursorObj = connection.cursor()
# Show databases 
cursorObj.execute("SHOW DATABASES")
# Fetch all the databases 
databases = cursorObj.fetchall()
# Printing the list of databases
print("The list of databases are: ")
for database in databases:
    print(database[0])
# Disconnecting from server
connection.close()

Output

Following is the output of the above code −

The list of databases are: 
information_schema
mysql
mysqlpythondb
performance_schema
sqlserver
stdi
sys
textx
tut
tutorials
Advertisements