How to SHOW or LIST Tables in MySQL

There are two primary methods to view the tables in MySQL:

1. The MYSQL SHOW TABLES command.
2. Querying the Information_schema.tables table.

In this article, we are going to explore them both.

MySQL SHOW TABLES Command

The show tables command displays the list of all tables created in a database. The syntax is as follows:

SHOW [EXTENDED] | [FULL] TABLES
    [{FROM} database_name]
    [LIKE 'DBNamePattern']

In the syntax,

1. Modifiers: We can use any of the following options to view the tables:
   1. EXTENDED – this option populates the list of the tables that have been created by failed the ALTER TABLE statements.
   2. FULL – this option shows an additional column that provides the type of the table. It can be a Base Table, a View, or the System View.
2. FROM – if you want to populate the list of tables from the specific table, you can use the FROM keyword.
3. LIKE – if you want to populate the list of tables with a specific pattern in their names, you can use the LIKE keyword.

Let us see the examples.

Example 1: Show All Tables Created in Specific Database

To view all MySQL tables created in the Sakila database, use the FULL modifier and the FROM keyword. Run the following command:

mysql> SHOW TABLES FROM sakila;

Output

As you can see, the query populated the list of the tables from the Sakila database.

Example 2: Populate the List of Tables with a Specific Keyword Pattern

Suppose we want to get the list of tables which names start with the keyword film. We are using the LIKE keyword to match the pattern.

But when we use the LIKE keyword, we cannot use the FROM modifier. The query is as follows:

use sakila;
SHOW TABLES like 'film%'

Output

Example 3: Populate the List of Tables with its Type

To populate the list of tables with their type, we can use the FULL modifier.  Suppose we want to derive the list of tables with their types created in the Sakila database. See the following query:

mysql> SHOW FULL TABLES FROM sakila;

Output

Now, let us populate the list of tables from information_schema.tables.

Querying Information_schema.tables Table

To view MySQL tables, we can use the information_schema.tables table which contains the following information:

• Table_Schema: Name of the database or schema in which the table has been created.
• Table_name: Name of the table.
• Table_type: Type of the table. It can be any of the following:
  • System View: List of the system views. It contains the information of the database’s internal details. These views are created by combining multiple base tables with the database information.
  • Base table: A list of base tables can be user tables or system tables.
  • View: List of user-defined views.
• Engine: Name of the database engine in which the table has been created.
• Version: The version number of the .frm file of the table.
• Row_format: The format of the row storage. The row-storage format can be any of the following:
  • Fixed
  • Compressed
  • Redundant
  • Dynamic
  • Compact
• Table_rows: Count of rows within the table. If you are using InnoDB tables, it returns the estimated row counts. To get the exact row count, use the SELECT COUNT(*) query.
• Avg_row_length: Average row length of the data file.
• Data_length: If you are using MyISAM tables, the value of the data_length column is the length of the data file. If you are using the InnoDB tables, the value of the Data_length column is the amount of the size allocated to clustered index multiplied by the InnoDB page size.
• Max_data_length: The maximum allowed length of the data file. It is the number of bytes that can be stored in the table.
• Index_length: The length of the index. If you are using MyISAM tables, the value of the index_length column shows the size of the index. If you are using the InnoDB tables, the value of the index_length column is the total size of all non-clustered indexes multiplied by the page size.
• Data_free: The total number of allocated but unused bytes. If you are using the InnoDB table, the value of the data_free column is tablespace’s unused space in which the table has been created.
• Auto_increment: The value of the Auto_increment column is the next auto-increment value.
• Create_time: The date and time when the table has been created. When we restore the database, the value of the create_date will be the time when the database was restored.
• Update_time: The date and time when the data file was updated. It displays the timestamp of the last INSERT, UPDATE, or DELETE statements executed on the InnoDB tables.
• Check_Time: The date and time when the table was last checked.
• Table_collation: The name of the default collation of the table.
• Checksum: The checksum value.
• Create_Option: The additional configuration option which was used to create the table.
• Table_comment: The comment or information used while creating the table.

Example 1: Populate List of All Tables

Suppose we want to populate all tables created in all databases. The query is as follows:

mysql> select table_catalog, table_schema, table_name,table_type,create_time from information_schema.tables;

Output

As you can see, the query has populated the list of tables with the database name and create time. The query returned many records, so the output is trimmed.

Example 2: Populate List of All Tables Created in Specific Database

To populate the list of tables created in the Sakila database, you can apply the filter on the Table_Schema column:

mysql> select table_catalog, table_schema, table_name,table_type,create_time from information_schema.tables WHERE table_schema='sakila';

Output

As you can see, the query has returned the list of tables and views created in the Sakila database, its creation time, and type.

Example 3: Populate List of All Tables with Row Count

We can include the Table_rows column to view the row count of MySQL tables. Suppose you want to get the list of tables which name starts with film. The output must include the number of records within those tables.

mysql> select table_catalog, table_schema, table_name,table_type,table_rows from information_schema.tables WHERE table_schema='sakila' and table_name like 'film%' and table_type='BASE TABLE';

Output

Example 4: Populate the Count of Tables and Views

For example, we want to get the count of the tables and views created in the database. To do that, run the following query:

mysql> select table_schema as 'Database OR Schema name', count(table_type) as 'Tables and Views', table_type as 'Object Type' from information_schema.tables  group by table_type,table_schema;

Output

As you can see in the above image, the query has populated the count of tables and views created in all databases.

Example 5: Tables with Their Size

Now, we want to populate the list of tables created in the Sakila database with their size. To generate the list, run the following query:

mysql> SELECT Table_schema as 'Database Name',  TABLE_NAME AS 'Table',   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 ) AS 'Size in KB' FROM   information_schema.TABLES WHERE TABLE_SCHEMA = 'sakila' and table_type='BASE TABLE' ORDER BY  (DATA_LENGTH + INDEX_LENGTH) DESC;

Output

As you can see, the query has returned the database name, table name, and size.

Summary

Thus, we have defined and learned to use the methods of populating the list of tables created in the database. We explored the SHOW TABLES command, and then we examined the method of querying the Information_schema.tables table. We hope that these tips will be useful in your workflow.

If you want to learn more about tables in SQL, read the article about temporary tables in SQL Server. And if you’d like to add something, share your work tips, or discuss these methods, you are welcome to the Comments section.

Tags: mysql, sql server tables, view tables Last modified: June 27, 2023