MySQL − Show Tables

MySQL Show Tables Statement

In MySQL, we use the SHOW TABLES command to retrieve the names of tables that are present in a specific database. This command is useful in various situations, such as:

• When we want to view names of tables present in a database to verify if a specific table exists or not.

• When we want to display additional information about each table present in a database, we use the SHOW TABLES command with the MySQL FULL modifier.

• Additionally, we can use the SHOW TABLES command with WILDCARDS to filter and display only the tables that match a specific pattern.

Syntax

Following is the syntax of MySQL SHOW TABLES command−

SHOW TABLES;

Before proceeding with the examples, assume that the following tables exist in two databases, testdb1 and testdb2:

Example

First of all, we are changing the database to testdb1 to perform the SHOW TABLES operation on it.

mysql> USE testdb1;
Database changed

Now, execute the following query to list down all the tables from testdb1 database.

SHOW TABLES;

Output

Following are the tables that are present in the testdb1 database −

SHOW TABLES with FULL modifier

In MySQL, we use the optional FULL modifier along with the SHOW TABLES command to display a second output column that contains additional information about the tables present in a database, such as their types: BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

Example

In the following query, we are using the FULL modifier along with the SHOW TABLES command to list the tables and their types in the testdb1 database.

SHOW FULL TABLES;

Output

Following is the output of the above query −

SHOW TABLES in different Database

In MySQL, we can retrieve the list of tables present in another database. To do so, we need to use the IN operator or the FROM clause in conjunction with the SHOW TABLES statement.

Example

In the following query, we are fetching the list of tables that exist in another database testdb2, using the SHOW TABLES command with IN operator.

SHOW TABLES IN testdb2;

Output

Following are the names of the tables that are present in testdb2 database −

Example

We can also perform the above operation using the SHOW TABLES command with FROM clause.

SHOW TABLES FROM testdb2;

Output

As we can observe, both outputs are the same.

SHOW TABLES using Pattern Matching

In some scenarios where there are large amount of tables present in a database, and we want to retrieve only specific tables, we use the LIKE operator with WILDCARD characters such as '%'. These wildcards will filter and display only the tables that match a specific pattern."

Example

In the following query, we are using the LIKE operator with SHOW TABLES command to select all the tables (in testdb1 database) where the name starts with "stud".

SHOW TABLES IN testdb1 LIKE "stud%";

Output

Following are the tables present in testdb1 database whose name starts with "stud" −

Example

Here, we are trying to retrieve the tables from testdb2 database where the name starts with "stud" −

SHOW TABLES IN testdb2 LIKE "stud%";

Output

This will produce following result −

Example

We are using the SHOW TABLES along with WHERE clause to check if there is a table named "employee_remarks" in testdb1 database −

SHOW TABLES FROM testdb1 WHERE Tables_in_testdb1 = "employee_remarks";

Output

This will produce following result −

Showing tables Using a Client Program

Besides showing the list of tables present in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW TABLES operation.

Syntax

Following are the syntaxes to Show list of tables in MySQL Database in various programming languages −

$sql = "SHOW TABLES FROM DATABASE";
$mysqli->query($sql);

sql= "SHOW TABLES FROM DATABASE";
con.query(sql);

String sql = "SHOW TABLES FROM DATABASE";
statement.executeQuery(sql);

show_table_query = "SHOW TABLES FROM DATABASE"
cursorObj.execute(show_table_query);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = "SHOW TABLES FROM TUTORIALS";
if ($result = $mysqli->query($sql)) {
    printf("Show table executed successfully.
");
    while ($row = mysqli_fetch_array($result)) {
        print_r($row);
    }
}
if ($mysqli->errno) {
    printf("Could not show table: %s
", $mysqli->error);
}
$mysqli->close();  

Show table executed successfully.
Array
(
    [0] => articles
    [Tables_in_tutorials] => articles
)
Array
(
    [0] => courses
    [Tables_in_tutorials] => courses
)
Array
(
    [0] => customers
    [Tables_in_tutorials] => customers
)
Array
(
    [0] => customerss
    [Tables_in_tutorials] => customerss
)
Array
(
    [0] => demo_table
    [Tables_in_tutorials] => demo_table
)
Array
(
    [0] => employee
    [Tables_in_tutorials] => employee
)
Array
(
    [0] => films
    [Tables_in_tutorials] => films
)
Array
(
    [0] => films_watched
    [Tables_in_tutorials] => films_watched
)
Array
(
    [0] => myplayers
    [Tables_in_tutorials] => myplayers
)
Array
(
    [0] => new_tutorial_tbl
    [Tables_in_tutorials] => new_tutorial_tbl
)
Array
(
    [0] => orders
    [Tables_in_tutorials] => orders
)
Array
(
    [0] => persons
    [Tables_in_tutorials] => persons
)
Array
(
    [0] => products
    [Tables_in_tutorials] => products
)
Array
(
    [0] => sales
    [Tables_in_tutorials] => sales
)
Array
(
    [0] => students
    [Tables_in_tutorials] => students
)        

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("--------------------------");

  sql = "CREATE DATABASE TUTORIALS;"
  con.query(sql);

  sql = "USE TUTORIALS;"
  con.query(sql);

  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);"
  con.query(sql);

  sql = "CREATE TABLE ORDERS (OID INT NOT NULL, CUSTOMER_ID INT);"
  con.query(sql);

  sql = "SHOW TABLES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});               

Connected!
--------------------------
[
  { Tables_in_tutorials: 'customers' },
  { Tables_in_tutorials: 'orders' }
]         

import java.sql.*;
public class JDBCconnection {
public static void main(String[] args){
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String username = "root";
    String password = "password";
    try{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        System.out.println("Connected successfully...!");

        //show table statement...!
        String sql = "SHOW TABLES FROM TUTORIALS";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Tables in the current database: ");
        while(resultSet.next()) {
            System.out.print(resultSet.getString(1));
            System.out.println();
        }
        connection.close();
    }
    catch(Exception e){
        System.out.println(e);
    }
}
}                              

Connected successfully...!
Tables in the current database: 
articles
blackpink
blog
courses
customer
customers
data   

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
#Creating a cursor object 
cursorObj = connection.cursor()
cursorObj.execute("SHOW TABLES")
tables = cursorObj.fetchall()
print("Tables in the database are:")
for table in tables:
    print(table[0])
cursorObj.close()
connection.close()                                         

Tables in the database are:
books
novel
novels
tutorials_tbl