SHOW TABLES

The SHOW TABLES statement reference.

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>]