Get table names using SELECT statement in MySQL

Question

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

User · Answer

Try   select   from information schema tables   See  http   dev mysql com doc refman 5 0 en information-schema html

User · Answer

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

User · Answer

Besides using the INFORMATION SCHEMA table  to use SHOW TABLES to insert into a table you would use the following   lt  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        gt

User · Answer

There is yet another simpler way to get table names  SHOW TABLES FROM  lt database name gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT table name  FROM information schema tables  WHERE table schema    DATABASE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Get table names using SELECT statement in MySQL

Examples related to mysql