Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Show Tables

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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:
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 
PHPNodeJSJavaPython
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);
Example
Following are the programs 
PHPNodeJSJavaPython
$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
)        
﻿
﻿
﻿
﻿
﻿
﻿

Databases	testdb1	testdb2
Tables	employee_remarks	employee_age
	employee_salary	students_marks
	students_attendance
	students_fees
	students_remarks

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Show Tables

MySQL Show Tables Statement

Syntax

Example

Output

SHOW TABLES with FULL modifier

Example

Output

SHOW TABLES in different Database

Example

Output

Example

Output

SHOW TABLES using Pattern Matching

Example

Output

Example

Output

Example

Output

Showing tables Using a Client Program

Syntax

Example

Output