SQL Standard

List the Tables in SQL

An SQL table refers to a fundamental database object that allows us to store the data in a structured format. A table in a relational database is comprised of rows and columns where each row represents a record, and a column denotes a specific attribute of the record.

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 the MySQL version 8.0.

SQL Show Table

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:

SHOW TABLES [FROM db_name] [LIKE 'pattern'];

In the 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 shows 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:

mysql> show tables;

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:

mysql> show tables like '%film%';

The previous 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:

SHOW TABLES FROM employees;

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:

SELECT table_name

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list