[mysql] 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? */

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

For fetching the name of all tables:

SELECT table_name 
FROM information_schema.tables;

If you need to fetch it for a specific database:

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

Output:

+--------------------+
| table_name         |
+--------------------+
| myapp              |
| demodb             |
| cliquein           |
+--------------------+
3 rows in set (0.00 sec)

This below query worked for me. This can able to show the databases,tables,column names,data types and columns count.

**select table_schema Schema_Name ,table_name TableName,column_name ColumnName,ordinal_position "Position",column_type DataType,COUNT(1) ColumnCount
FROM information_schema.columns
GROUP by table_schema,table_name,column_name,ordinal_position, column_type;**

To insert, update and delete do the following:

$teste = array('LOW_PRIORITY', 'DELAYED', 'HIGH_PRIORITY', 'IGNORE', 'INTO', 'INSERT', 'UPDATE', 'DELETE', 'QUICK', 'FROM');
$teste1 = array("\t", "\n", "\r", "\0", "\x0B");
$strsql = trim(str_ireplace($teste1, ' ', str_ireplace($teste, '', $strsql)));
$nomeTabela = substr($strsql, 0, strpos($strsql, ' '));

print($nomeTabela);
exit;

Try:

select * from information_schema.tables

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

There is yet another simpler way to get table names

SHOW TABLES FROM <database_name>

MySQL INFORMATION_SCHEMA.TABLES table contains data about both tables (not temporary but permanent ones) and views. The column TABLE_TYPE defines whether this is record for table or view (for tables TABLE_TYPE='BASE TABLE' and for views TABLE_TYPE='VIEW'). So if you want to see from your schema (database) tables only there's the following query :

SELECT *
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='myschema'

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);
 }
?>

I think it may be helpful to point out that if you want to select tables that contain specific words you can easily do it using the SELECT (instead of SHOW). Below query easily narrows down the search to tables that contain "keyword"

SELECT *
FROM information_schema.tables
WHERE table_name like "%keyword%"

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'DATABASE'

Take a look at the table TABLES in the database information_schema. It contains information about the tables in your other databases. But if you're on shared hosting, you probably don't have access to it.

I think you can get the data you want from INFORMATION_SCHEMA TABLES.

You can find more info here: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html