SHOW TABLES

Function

Views the list of tables in the current database or schema.

Precautions

If db_name is not specified, the list of tables in the current database (or schema) is queried.

Syntax

SHOW [FULL] TABLES
   [{FROM | IN} db_name]
   [LIKE 'pattern' | WHERE expr]

Parameter Description

db_name

Specifies the database name (or schema). This parameter is optional. If it is not specified, the current database or schema is queried.
LIKE 'pattern'

The patternp matches the first column (column name: 'Tables_in_dbname [pattern]') in the displayed result.

Examples

--Create a simple table:
MogDB=# CREATE SCHEMA tst_schema;
MogDB=# SET SEARCH_PATH TO tst_schema;

MogDB=# CREATE TABLE tst_t1
MogDB-# (
MogDB(# id int primary key,
MogDB(# name varchar(20) NOT NULL,
MogDB(# addr text COLLATE "de_DE",
MogDB(# phone text COLLATE "es_ES",
MogDB(# addr_code text
MogDB(# );
MogDB=# CREATE VIEW tst_v1 AS SELECT * FROM tst_t1;
MogDB=# CREATE TABLE t_t2(id int);

--View the list of tables in the database (or schema).
MogDB=# show tables;
 Tables_in_tst_schema 
----------------------
 tst_t1
 tst_v1
 t_t2

MogDB=# show full tables;
 Tables_in_tst_schema | Table_type 
----------------------+------------
 tst_t1               | BASE TABLE
 tst_v1               | VIEW
 t_t2                 | BASE TABLE

MogDB=# show full tables in tst_schema;
 Tables_in_tst_schema | Table_type 
----------------------+------------
 tst_t1               | BASE TABLE
 tst_v1               | VIEW
 t_t2                 | BASE TABLE
 
--Fuzzy match and filtering
MogDB=# show full tables like '%tst%';
 Tables_in_tst_schema (%tst%) | Table_type 
------------------------------+------------
 tst_t1                       | BASE TABLE
 tst_v1                       | VIEW

MogDB=# show full tables where Table_type='VIEW';
 Tables_in_tst_schema | Table_type 
----------------------+------------
 tst_v1               | VIEW