Get table names using SELECT statement in MySQL

In MySQL, I know I can list the tables in a database with:

SHOW TABLES

However, I want to insert these table names into another table, for instance:

INSERT INTO metadata(table_name) SHOW TABLES /* does not work */

Is there a way to get the table names using a standard SELECT statement, something like:

INSERT INTO metadata(table_name) SELECT name FROM table_names /* what should table_names be? */

Solution 1:

To get the name of all tables use:

SELECT table_name FROM information_schema.tables;

To get the name of the tables from a specific database use:

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

Now, to answer the original question, use this query:

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

For more details see: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Solution 2:

Try:

select * from information_schema.tables

See: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Solution 3:

if we have multiple databases and we need to select all tables for a particular database we can use TABLE_SCHEMA to define database name as:

select table_name from information_schema.tables where TABLE_SCHEMA='dbname';