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 pattern matches the first column (column name: 'Tables_in_dbname [pattern]') in the displayed result.

Examples

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

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

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

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

openGauss=# 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
openGauss=# show full tables like '%tst%';
 Tables_in_tst_schema (%tst%) | Table_type 
------------------------------+------------
 tst_t1                       | BASE TABLE
 tst_v1                       | VIEW

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