Get table names using SELECT statement in MySQL

Besides using the INFORMATION_SCHEMA table, to use SHOW TABLES to insert into a table you would use the following

<?php
 $sql = "SHOW TABLES FROM $dbname";
 $result = mysql_query($sql);
 $arrayCount = 0
 while ($row = mysql_fetch_row($result)) {
  $tableNames[$arrayCount] = $row[0];
  $arrayCount++; //only do this to make sure it starts at index 0
 }
 foreach ($tableNames as &$name {
  $query = "INSERT INTO metadata (table_name) VALUES ('".$name."')";
  mysql_query($query);
 }
?>

Try:

select * from information_schema.tables

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

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';

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