Complete guide to listing tables in MariaDB. Complete SHOW TABLES syntax reference with LIKE patterns, WHERE conditions, and filtering options.
Syntax
SHOW [FULL] TABLES [FROM db_name]
[LIKE 'pattern' | WHERE expr]
Description
SHOW TABLES lists the tables, and in a given database.
SHOW TABLES lists the tables (only non-TEMPORARY tables are shown), and in a given database.
The LIKE clause, if present on its own, indicates which table names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in . For example, when searching for tables in the test database, the column name for use in the WHERE and LIKE clauses will be Tables_in_test
The FULL modifier is supported such that SHOW FULL TABLES displays a second output column. Values for the second column, Table_type, are BASE TABLE for a table, VIEW for a and SEQUENCE for a .
You can also get this information using:
See for more details.
If you have no privileges for a base table or view, it does not show up in the output from SHOW TABLES or mariadb-show db_name.
The table, as well as the statement, provide extended information about tables.
SHOW TABLES WHERE Tables_in_test LIKE 'a%';
+----------------------+
| Tables_in_test |
+----------------------+
| animal_count |
| animals |
| are_the_mooses_loose |
| aria_test2 |
+----------------------+
SHOW FULL TABLES;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| s1 | SEQUENCE |
| student | BASE TABLE |
| v1 | VIEW |
+----------------+------------+
CREATE TABLE t (t INT(11));
CREATE TEMPORARY TABLE t (t INT(11));
CREATE TEMPORARY TABLE te (t INT(11));
SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t |
+----------------+
CREATE TABLE t (t INT(11));
CREATE TEMPORARY TABLE t (t INT(11));
CREATE TEMPORARY TABLE te (t INT(11));
SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| te |
| t |
| t |
+----------------+