Summary: in this tutorial, you will learn how to use the MariaDB show tables statement to list tables, views, and sequences in a specific database.
Introduction to the MariaDB show tables statement
The show tables statement allows you to list the non-temporary tables, views, and sequences from a database.
Here is the syntax of the show tables statement:
show [full] tables
Code language: SQL (Structured Query Language) (sql)[from database_name]
[like ‘pattern’ | where search_expression];
If you already selected a database, the show tables statement returns the tables, views, and sequences in the current database:
show tables;
Code language: SQL (Structured Query Language) (sql)In case you haven’t connected to any particular database, you can use the from clause to specify the name of the database from which you want to show the tables, views, and sequences:
show tables
from database_name;
Code language: SQL (Structured Query Language) (sql)The full option instructs the show tables statement to include an additional column called table_type in the result set. The table_type can be the base table, view, or sequence.
show full tables
from database_name;
Code language: SQL (Structured Query Language) (sql)The like or where clause allows you to specify a search condition to find tables, views, and sequences. You cannot use both of these clauses at the same time.
You use the like clause when you want to match tables, views, and sequences by a pattern. And you use the where clause when you want to form a more complex search condition.
MariaDB show tables statement example
Let’s take some examples of using the show tables statement.
A) Using MariaDB show tables statement to list tables in the current database
First, connect to the nation sample database.
Second, use the show tables statement to list all tables, views, and sequences in the nation database:
show tables;
Code language: SQL (Structured Query Language) (sql)Third, use the show tables statement with the full option:
show full tables;
Code language: SQL (Structured Query Language) (sql)B) Using MariaDB show tables statement with a like clause
The following statement uses the show tables with a like clause to find tables, view, and sequences whose names start with the word country:
show full tables
like 'country%'
Code language: SQL (Structured Query Language) (sql)C) Using MariaDB show tables statement with a where clause
The following statement uses the show tables statement with a where clause to list all tables in the nation database:
show full tables
where table_type = 'base table';
Code language: SQL (Structured Query Language) (sql)And this statement lists all views in the nation database:
show full tables
where table_type = 'view';
Code language: SQL (Structured Query Language) (sql)In this tutorial, you learned how to use the MariaDB show tables statement to list tables, views, and sequences in a specific database.
