Get table names using SELECT statement in MySQL

The Question :

277 people think this question is useful

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

The Question Comments :

The Answer 1

417 people think this answer is useful

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

The Answer 2

147 people think this answer is useful

Try:

select * from information_schema.tables

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

The Answer 3

21 people think this answer is useful

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

The Answer 4

11 people think this answer is useful

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 &amp;$name {
  $query = "INSERT INTO metadata (table_name) VALUES ('".$name."')";
  mysql_query($query);
 }
?>

The Answer 5

8 people think this answer is useful

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.

The Answer 6

6 people think this answer is useful

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

The Answer 7

5 people think this answer is useful
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'DATABASE'

The Answer 8

4 people think this answer is useful

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'

The Answer 9

3 people think this answer is useful

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

The Answer 10

2 people think this answer is useful

There is yet another simpler way to get table names

SHOW TABLES FROM <database_name>

The Answer 11

0 people think this answer is useful

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

The Answer 12

0 people think this answer is useful

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)

The Answer 13

-3 people think this answer is useful

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;

Tags:

Add a Comment