Khóa học miễn phí MySQL – Show Tables nhận dự án làm có lương

MySQL − Show Tables

Table of content


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:

Databases testdb1 testdb2
Tables employee_remarks employee_age
employee_salary students_marks
students_attendance
students_fees
students_remarks

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 −

Tables_in_testdb1
employee_remarks
employee_salary
students_attendance
students_fees
students_remarks

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 −

Tables_in_testdb1 Table_type
employee_remarks BASE TABLE
employee_salary BASE TABLE
students_attendance BASE TABLE
students_fees BASE TABLE
students_remarks BASE TABLE

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 −

Tables_in_testdb2
employee_age
students_marks

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.

Tables_in_testdb2
employee_age
students_marks

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” −

Tables_in_testdb1 (stud%)
students_attendance
students_fees
students_remarks

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 −

Tables_in_testdb2 (stud%)
students_marks

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 −

Tables_in_testdb1
employee_remarks

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 −

To show the list of tables in MySQL Database through a PHP program, we need to execute SHOW TABLES statement using the mysqli function query() as −

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

To show the list of tables in MySQL Database through a Node.js program, we need to execute SHOW TABLES statement using the query() function of the mysql2 library as −

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

To show the list of tables in MySQL Database through a Java program, we need to execute SHOW TABLES statement using the JDBC function executeUpdate() as −

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

To show the list of tables in MySQL Database through a Java program, we need to execute SHOW TABLES statement using the execute() function of the MySQL Connector/Python as −

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

Output

The output obtained is as follows −

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);
  });
});               

Output

The output produced is as follows −

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);
    }
}
}                              

Output

The output obtained is as shown below −

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

Output

Following is the output of the above code −

Tables in the database are:
books
novel
novels
tutorials_tbl

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc