SHOW TABLES

Updated on 2022-11-18 GMT+08:00

View PDF

Syntax

SHOW TABLES [ (FROM | IN) schema ] [ LIKE pattern [ESCAPE escapeChar] ]

Description

This expression is used to list all tables in a specified schema. If no schema is specified, the current schema is used by default.

The optional parameter like is used for keyword-based matching.

Example

--Create a test table.
Create table show_table1(a int);
Create table show_table2(a int);
Create table showtable5(a int);
Create table intable(a int);
Create table fromtable(a int);
 
-- Match a single character '_'.
show tables in  default like 'show_table_';
  Table    
-------------
 show_table1 
 show_table2 
(2 rows)
 
-- Match multiple characters '*' and '%'.
show tables in default like 'show%';
Table    
-------------
 show_table1 
 show_table2 
 showtable5  
(3 rows)
 
show tables in default like 'show*';
Table    
-------------
 show_table1 
 show_table2 
 showtable5  
(3 rows)
 
-- The escape character is used. In the second example, '_' is used as the filter condition. The result set does not contain showtable5.
show tables in default like 'show_%';
    Table    
-------------
 show_table1 
 show_table2 
 showtable5  
(3 rows)
 
show tables in default like 'show$_%' ESCAPE '$';
    Table    
-------------
 show_table1 
 show_table2 
(2 rows)
 
-- If multiple conditions are met, query the tables whose names start with show_ or in in default.
show tables in default like 'show$_%|in%' ESCAPE '$';
    Table    
-------------
 intable     
 show_table1 
 show_table2 
(3 rows)

Parent topic: DDL Syntax

Previous topic: SHOW SCHEMAS (DATABASES)

Next topic: SHOW TBLPROPERTIES TABLE|VIEW