MySQL - SHOW TABLE STATUS Statement

Table of content

MySQL SHOW TABLE STATUS Statement

The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.

The SHOW TABLE STATUS Statement of MySQL provides information about the non-TEMPORARY tables in a database.

Syntax

Following is the syntax of the SHOW TABLES Statement −

SHOW TABLE STATUS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

Example

Assume we have created 4 tables in the current database using the CREATE statement as shown below −

CREATE TABLE TestTable1(value VARCHAR(10));
CREATE TABLE TestTable2(value VARCHAR(10));
CREATE TABLE TestTable3(value VARCHAR(10));
CREATE TABLE TestTable4(value VARCHAR(10));

Following statement displays the information about the non-temporary trebles in the current database −

SHOW TABLE STATUS\G;

Output

Once the query is executed, it will produce the following output −

*************** 1. row ***************
                  Name: testtable1
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:04:03 
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 2. row ***************
                  Name: testtable2
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:04:08
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 3. row ***************
                  Name: testtable3
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:04:21
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 4. row ***************
                  Name: testtable4
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:12
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:

FROM or IN clause

You can retrieve information about the non-TEMPORARY tables from a specific database using the FROM clause.

Example

Assume we have created a database named demo using the CREATE DATABASE statement −

CREATE DATABASE demo;

Now, let us create tables in it, using the CREATE TABLE statement −

CREATE TABLE demo.myTable1 (data INT);
CREATE TABLE demo.myTable2 (data INT);
CREATE TABLE demo.myTable3 (data INT);
CREATE TABLE demo.myTable4 (data INT);

Following query lists out the information about tables in the database "demo" −

SHOW TABLE STATUS FROM demo\G;

Output

The above query produces the output shown below −

*************** 1. row ***************
                  Name: mytable1
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:09:34 
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 2. row ***************
                  Name: mytable2
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:09:47
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 3. row ***************
                  Name: mytable3
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:12
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 4. row ***************
                  Name: mytable4
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:12
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:

You can also use the IN clause instead of FROM as −

SHOW TABLE STATUS IN demo\G;

Output

Following is the output of the above query −

*************** 1. row ***************
                  Name: mytable1
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:09:34 
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 2. row ***************
                  Name: mytable2
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:09:47
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 3. row ***************
                  Name: mytable3
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:00
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 4. row ***************
                  Name: mytable4
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:12
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve information about specific tables. Following query retrieves description about the tables with name starting with "my".

use demo;
Database changed
SHOW TABLE STATUS LIKE 'my%'\G;

Output

The above query generates the following output −

*************** 1. row ***************
                  Name: mytable1
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:09:34
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:  
*************** 2. row ***************
                  Name: mytable2
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:09:47
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 3. row ***************
                  Name: mytable3
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:00
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
*************** 4. row ***************
                  Name: mytable4
				Engine: InnoDB
			   Version: 10
		    Row_format: Dynamic
			      Rows: 0
	    Avg_row_length: 0
		   Data_length: 16384
	   Max_data_length: 0
	      Index_length: 0
		     Data_free: 0
	    Auto_increment: NULL
		   Create_time: 2021-05-13 20:10:12
		   Update_time: NULL
		    Check_time: NULL
		     Collation: utf8mb4_0900_ai_ci
		      Checksum: NULL
	    Create_options:
		       Comment:
Advertisements