Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get table names using SELECT statement in MySQL

Tags:

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? */ 
like image 270
Mike Chamberlain Avatar asked Nov 30 '11 23:11

Mike Chamberlain


People also ask

How do I get a list of table names in SQL?

In MySQL, there are two ways to find the names of all tables, either by using the "show" keyword or by query INFORMATION_SCHEMA. In the case of SQL Server or MSSQL, You can either use sys. tables or INFORMATION_SCHEMA to get all table names for a database.

How do I query a table in MySQL?

The first command you will need to use is the SELECT FROM MySQL statement that has the following syntax: SELECT * FROM table_name; This is a basic MySQL query which will tell the script to select all the records from the table_name table.

How do I query all tables in SQL?

The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views. You do this by specifying the information schema, then the “tables” view. Here's an example. SELECT table_name, table_schema, table_type FROM information_schema.

How do I select a specific table in MySQL?

We use the SELECT * FROM table_name command to select all the columns of a given table. In the following example we are selecting all the columns of the employee table. mysql> SELECT * FROM employee; And we get the following output.


2 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: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

like image 175
Murilo Garcia Avatar answered Oct 05 '22 18:10

Murilo Garcia


Try:

select * from information_schema.tables 

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

like image 36
Nthalk Avatar answered Oct 05 '22 17:10

Nthalk