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

14 Answers

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:

Try:

select * from information_schema.tables 

See:

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

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

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.

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

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' 

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

You can find more info here:

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) 

There is yet another simpler way to get table names

SHOW TABLES FROM <database_name> 

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%" 

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

Yes, using information_schema.TABLES. This works even on a cloud solution like Skyvia shown below:

With this you can simply use a SELECT statement like the above to add it up for your INSERT statement. But change the table_schema value to match the database name for your actual setup.

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;