MySQL Table Names with SELECT

2024-09-01

SHOW TABLES;

This statement will return a list of all table names in the current database.

If you want to get the names of tables that match a specific pattern, you can use the LIKE operator:

SHOW TABLES LIKE 'user%';

This statement will return the names of all tables that start with "user".

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'users';

Example Codes for Getting Table Names in MySQL

Using `SHOW TABLES`

SHOW TABLES;

Example:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_your_database |
+-----------------------+
| customers             |
| orders                |
| products              |
+-----------------------+
3 rows in set

Using `INFORMATION_SCHEMA.TABLES`

This method provides more flexibility and allows you to filter based on specific criteria:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name';

mysql> SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'my_database';
+-----------------------+
| table_name             |
+-----------------------+
| customers             |
| orders                |
| products              |
+-----------------------+
3 rows in set

Filtering Table Names

You can use LIKE to filter table names based on patterns:

SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'order%';

mysql> SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'order%';
+-----------------------+
| table_name             |
+-----------------------+
| orders                |
+-----------------------+
1 row in set

You can use INFORMATION_SCHEMA.COLUMNS to get more details about table columns, such as their data types and constraints:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'your_database_name'
  AND table_name = 'your_table_name';

mysql> SELECT column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_schema = 'my_database'
      AND table_name = 'customers';
+-----------------+--------------+--------------+
| column_name      | data_type     | is_nullable |
+-----------------+--------------+--------------+
| customer_id      | int(11)      | NO           |
| customer_name    | varchar(100) | YES          |
| customer_email   | varchar(100) | YES          |
+-----------------+--------------+--------------+
3 rows in set

Using the DATABASE() Function

This method is especially useful when you need to dynamically get the current database name:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = DATABASE();

This will return all table names from the current database.

Using the INFORMATION_SCHEMA.SCHEMATA Table

If you want to list all tables in multiple databases at once, you can query the INFORMATION_SCHEMA.SCHEMATA table:

SELECT table_name
FROM information_schema.tables
WHERE table_schema IN (SELECT schema_name FROM information_schema.schemata);

Using the MySQL Command-Line Client

If you're working in the MySQL command-line client, you can directly use the SHOW TABLES command without the SELECT statement:

SHOW TABLES;

Using a Programming Language and MySQL Connector

If you're using a programming language like Python, Java, or PHP, you can use the corresponding MySQL connector to execute the SHOW TABLES query or query the INFORMATION_SCHEMA tables.

For example, in Python using the mysql-connector-python library:

import mysql.connector

mydb = mysql.connector.connect(
  host="your_host",
  user="your_user",
  password="your_password",
  database="your_database"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW    TABLES")

for x in mycursor:
  print(x)

mysql