​
Syntax

SHOW [FULL] [TEMPORARY] TABLES
[{FROM | IN} <database>]
[[NOT] LIKE | ILIKE '<pattern>']
[LIMIT <N>]
[INTO OUTFILE <filename>]
[FORMAT <format>]

​
Clauses

​
FROM | IN

The FROM or IN clause specifies the database to show tables from. If not specified, tables from the current database are shown. 
SHOW TABLES FROM taco_shop;

​
LIKE | ILIKE

The LIKE or ILIKE clause filters table names based on a pattern. ILIKE performs case-insensitive matching. 
SHOW TABLES LIKE '%taco%';
SHOW TABLES ILIKE '%TACO%';

​
NOT LIKE | NOT ILIKE

The NOT LIKE or NOT ILIKE clause excludes tables whose names match the specified pattern. 
SHOW TABLES NOT LIKE '%burrito%';

​
LIMIT

The LIMIT clause restricts the number of rows in the result set. 
SHOW TABLES LIMIT 3;

​
INTO OUTFILE

The INTO OUTFILE clause writes the result to a file. 
SHOW TABLES INTO OUTFILE 'taco_tables.txt';

​
FORMAT

The FORMAT clause specifies the output format. 
SHOW TABLES FORMAT Pretty;

​
Examples

Show tables containing ‘taco’ in their names: 
SHOW TABLES FROM taco_shop LIKE '%taco%'
Result: 
┌─name───────────────┐
│ taco_ingredients   │
│ taco_orders        │
└────────────────────┘
Show tables containing ‘TACO’ in their names (case-insensitive): 
SHOW TABLES FROM taco_shop ILIKE '%TACO%'
Result: 
┌─name─────┐
│ taco_ingredients  │
│ taco_orders       │
│ TacoSpecials      │
└───────────┘
Show tables not containing ‘salsa’ in their names: 
SHOW TABLES FROM taco_shop NOT LIKE '%salsa%'
Result: 
┌─name──────────────┐
│ taco_ingredients  │
│ taco_orders       │
│ tortilla_types    │
└───────────────────┘
Show the first two tables: 
SHOW TABLES FROM taco_shop LIMIT 2
Result: 
┌─name──────────────┐
│ taco_ingredients  │
│ taco_orders       │
└───────────────────┘

​
Notes

  • If the FROM clause is not specified, the query returns the list of tables from the current database.
  • This statement is equivalent to the following query: 
    SELECT name FROM system.tables
[WHERE name [NOT] LIKE | ILIKE '<pattern>']
[LIMIT <N>]
[INTO OUTFILE <filename>]
[FORMAT <format>]