Tables are the main building block of any functional relational database. Hence, you will encounter yourself interacting with them than any other objects within the databases.
One common task that you might encounter when working with SQL tables is fetching all the tables within a given database.
In this tutorial, we will discover all the various methods and techniques that we can use to show the tables within an SQL database. For this post, we will use MySQL version 8.0.
SQL SHOW TABLES
In SQL the SHOW TABLES is a command that is supported by various relational database engines such as MySQL.
It allows us to list all the available tables within a given database. It is quite useful when you need a quick glance of the available tables, especially if you are unfamiliar with the database schema.
Basic Syntax
It is good to keep in mind that although the ability to list the tables within a database is supported by nearly all database engine, the command or SQL statement may vary.
However, if you are working in MySQL, you can use the native SHOW TABLES command as shown in the following syntax:
In the given example syntax, we start by calling the SHOW TABLES command. This is the primary statement to list the available tables.
Next, we specify the database that we wish to show. If we omit the database name, the statement will show the tables from the currently selected database.
The SHOW TABLE statement also supports the use of pattern-like filtering using the LIKE statement. This allows us to specify a filter for the tables that match the specified pattern. We can also use the wildcard characters such as “%” for zero or more characters, an underscore for a single character, etc.
Example 1: Show All Tables in a Database
Suppose we are using the Sakila sample database in MySQL to show all the tables within the database without any additional filtering. We can use a query as shown in the following:
The command returns a list of all tables in the current database. An example output is as follows:
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| emp |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
24 rows in set (0.00 sec)
This returns all the tables that are stored in the Sakila sample database.
Example 2: Pattern Matching
Suppose we want to filter any table that might contain the word “film” in the name. We can use the LIKE clause with a wildcard character as follows:
The given command lists all tables in the current database whose names contain the word “film” anywhere in the table name.
An example output is as follows:
| Tables_in_sakila (%film%) |
+----------------------------+
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| nicer_but_slower_film_list |
| sales_by_film_category |
+----------------------------+
7 rows in set (0.00 sec)
Example 3: Different Database
To list the tables from a specific database other than the current one, we can use the FROM clause as follows:
In this case, the clause returns a table list from the “employees” table even if we are currently using the Sakila database.
Using the SQL Query to List the Tables
If you do not have access to the MySQL CLI, you can use the native SQL queries to list all the tables from a given database.
The SQL statement is as follows:
FROM information_schema.tables
WHERE table_schema = 'database_name';
This should return all the tables from the specified schema name.
Conclusion
In this tutorial, we learned all about the SQL SHOW TABLES command which allows us to retrieve the list of tables from a given database.
