tables

The tables view provides information about tables in databases. This view is read-only. All users have the read permission on this view. Some information in the view is obtained based on statistics. Run ANALYZE before checking the information. (If data is updated in the database, you are advised to delay running ANALYZE.)

**Table 1** information_schema.tables columns
Name	Type	Description
TABLE_CATALOG	varchar(512)	Name of the database. When lower_case_table_names is set to 0, the value of this column is case-sensitive. When lower_case_table_names is set to 1, the value of this column is case-insensitive.
TABLE_SCHEMA	varchar(64)	Name of the schema to which the table belongs. When lower_case_table_names is set to 0, the value of this column is case-sensitive. When lower_case_table_names is set to 1, the value of this column is case-insensitive.
TABLE_NAME	varchar(64)	Name of the table. When lower_case_table_names is set to 0, the value of this column is case-sensitive. When lower_case_table_names is set to 1, the value of this column is case-insensitive.
TABLE_TYPE	varchar(64)	Type of the table. BASE TABLE for a table, VIEW for a view, and SYSTEM VIEW for an INFORMATION_SCHEMA table. The views and tables under INFORMATION_SCHEMA are system views.
ENGINE	varchar(64)	Storage engine of the table.
VERSION	bigint unsigned	This column is not supported in the current version, and it is null by default.
ROW_FORMAT	varchar(10)	This column is not supported in the current version, and it is null by default.
TABLE_ROWS	bigint unsigned	If the table specifies a view, the value is null. Otherwise, the value is the number of live tuples.
AVG_ROW_LENGTH	bigint unsigned	If there is no live or dead tuple in the table, the value is null. Otherwise, the value is the average row length of the table.
DATA_LENGTH	bigint unsigned	If the table specifies a view, the value is null. Otherwise, the value is the data file length.
MAX_DATA_LENGTH	bigint unsigned	This column is not supported in the current version, and it is null by default.
INDEX_LENGTH	bigint unsigned	If the table specifies a view, the value is null. Otherwise, the value is the size of the index file.
DATA_FREE	bigint unsigned	If there is no live or dead tuple in the table, the value is null. Otherwise, the value is the number of bytes that have been allocated but not used.
AUTO_INCREMENT	bigint unsigned	Next automatic increment. If this column is not set, the value is null. Otherwise, the specified value takes effect.
CREATE_TIME	datetime	Time when the table is created. If a view is created, the actual table creation time is displayed. The value is null if it is a table or view provided by an M-compatible database.
UPDATE_TIME	datetime	Time when the table data was updated last time. The value is null if it is a table or view provided by an M-compatible database.
CHECK_TIME	datetime	This column is not supported in the current version, and it is null by default.
TABLE_COLLATION	varchar(32)	Collation of the table. The value is null if the table specifies a view. The value is null if the COLLATE clause is not used to specify the collation of columns when the specified table is created.
CHECKSUM	bigint unsigned	This column is not supported in the current version, and it is null by default.
CREATE_OPTIONS	varhcar(255)	Options of the table.
TABLE_COMMENT	varchar(2048)	Comment of the table.

Run ANALYZE to update the statistics and then check the view.

m_db=#  CREATE TABLE t1(a int);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES (1),(2);
INSERT 0 2
m_db=# ANALYZE;
ANALYZE
m_db=# SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,ENGINE FROM information_schema.tables WHERE TABLE_NAME='t1';
 TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | TABLE_ROWS | ENGINE
---------------+--------------+------------+------------+------------+--------
 m_db          | public       | t1         | BASE TABLE | 2          | Ustore
(1 row)
m_db=# DROP TABLE t1;
DROP TABLE

Parent topic: Information Schemas

Previous topic: statistics

Next topic: table_constraints