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? */
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.
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.
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.
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.
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
Try:
select * from information_schema.tables
See: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With