Get table names using SELECT statement in MySQL

Learn get table names using select statement in mysql with practical examples, diagrams, and best practices. Covers mysql development techniques with visual explanations.
Tags:
Categories:

How to List Table Names in MySQL Using SELECT Statements

Hero image for Get table names using SELECT statement in MySQL

Discover various SQL queries and methods to retrieve table names from your MySQL database, including standard SQL and MySQL-specific approaches.

Retrieving a list of table names within a database is a fundamental task for any database administrator or developer. While MySQL provides several ways to achieve this, understanding the different SELECT statement approaches can be particularly useful for scripting, automation, or when you need to query the information schema directly. This article will guide you through the most common and effective methods to get table names using SELECT statements in MySQL.

Using the INFORMATION_SCHEMA Database

MySQL's INFORMATION_SCHEMA is a virtual database that provides access to database metadata. It contains several tables that store information about all other databases, tables, columns, and other database objects. The TABLES table within INFORMATION_SCHEMA is precisely what we need to query for table names.

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
AND table_type = 'BASE TABLE';

Querying INFORMATION_SCHEMA.TABLES for base table names

Let's break down this query:

  • SELECT table_name: This specifies that we want to retrieve the name of each table.
  • FROM information_schema.tables: This indicates that we are querying the tables table within the information_schema database.
  • WHERE table_schema = 'your_database_name': This is a crucial filter. You must replace 'your_database_name' with the actual name of the database you are interested in. Without this, the query would return tables from all databases on the server.
  • AND table_type = 'BASE TABLE': This further refines the results to include only actual tables, excluding views or other temporary objects. If you also want to include views, you can remove this condition or change it to IN ('BASE TABLE', 'VIEW').
erDiagram
    INFORMATION_SCHEMA ||--o{ TABLES : contains
    TABLES { 
        VARCHAR table_schema PK
        VARCHAR table_name PK
        VARCHAR table_type
        DATETIME create_time
        BIGINT table_rows
    }
    TABLES ||--o{ COLUMNS : has
    COLUMNS { 
        VARCHAR table_schema PK
        VARCHAR table_name PK
        VARCHAR column_name PK
        VARCHAR data_type
    }

Simplified ER Diagram of INFORMATION_SCHEMA tables relevant to table metadata

Alternative MySQL-Specific Commands

While INFORMATION_SCHEMA is the standard SQL way, MySQL also offers simpler, non-standard commands that are often used for quick lookups. These are not SELECT statements in the traditional sense but are commands executed through the MySQL client that internally perform similar metadata lookups.

SHOW TABLES;
-- Or, to specify a database:
SHOW TABLES FROM your_database_name;

Using SHOW TABLES command

SHOW TABLES is a very common and convenient command. When executed after you've selected a database (e.g., using USE your_database_name;), it lists all tables in the current database. You can also explicitly specify the database name using SHOW TABLES FROM your_database_name;.

Retrieving Table Names with Specific Patterns

Sometimes you might need to find tables that match a certain naming pattern. You can achieve this by adding a LIKE clause to your INFORMATION_SCHEMA query.

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
AND table_type = 'BASE TABLE'
AND table_name LIKE 'prefix_%';

Finding tables with a specific prefix

In this example, LIKE 'prefix_%' will return all table names that start with 'prefix_'. You can use standard SQL wildcard characters:

  • %: Matches any sequence of zero or more characters.
  • _: Matches any single character.