Question
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? */
Answer
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>
