MySQL – SHOW Databases
Table of content
- MySQL SHOW Databases Statement
- MySQL SHOW SCHEMAS Statement
- Showing Databases Using a Client Program
”;
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 −
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 −
$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
”;