Select data from show tables MySQL query

Question

Is it possible to select from show tables in MySQL   SELECT   FROM  SHOW TABLES  AS  my tables    Something along these lines  though the above does not work  on 5 0 51a  at least

User · Answer

Yes  SELECT from table schema could be very usefull for system administration  If you have lot of servers  databases  tables    sometimes you need to DROP or UPDATE bunch of elements  For example to create query for DROP all tables with prefix name  wp old        SELECT concat  DROP TABLE    table name       FROM INFORMATION SCHEMA TABLES WHERE table schema     name of your database   AND table name LIKE  wp old

User · Answer

I don t understand why you want to use  SELECT   FROM as part of the statement   12 5 5 30  SHOW TABLES Syntax

User · Answer

I don t understand why you want to use  SELECT   FROM as part of the statement   12 5 5 30  SHOW TABLES Syntax

User · Answer

SELECT column comment FROM information schema columns WHERE table name    myTable  AND column name    myColumnName    This will return the comment on  myTable myColumnName

User · Answer

I think you want SELECT   FROM INFORMATION SCHEMA TABLES  See http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

I don t understand why you want to use  SELECT   FROM as part of the statement   12 5 5 30  SHOW TABLES Syntax

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES   That should be a good start  For more  check INFORMATION SCHEMA Tables

User · Answer

You can t put SHOW statements inside a subquery like in your example  The only statement that can go in a subquery is SELECT   As other answers have stated  you can query the INFORMATION SCHEMA directly with SELECT and get a lot more flexibility that way   MySQL s SHOW statements are internally just queries against the INFORMATION SCHEMA tables   User  physicalattraction has posted this comment on most other answers      This gives you  meta information about the tables  not the contents of the table  as the OP intended      physicalattraction   On the contrary  the OP s question does not say that they want to select the data in all the tables  They say they want to select from the result of SHOW TABLES  which is just a list of table names   If the OP does want to select all data from all tables  then the answer is no  you can t do it with one query  Each query must name its tables explicitly  You can t make a table name be a variable or the result of another part of the same query  Also  all rows of a given query result must have the same columns   So the only way to select all data from all tables would be to run SHOW TABLES and then for each table named in that result  run another query

User · Answer

I think you want SELECT   FROM INFORMATION SCHEMA TABLES  See http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

I think what you want is MySQL s information schema view s   http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

I think what you want is MySQL s information schema view s   http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

You may be closer than you think  mdash  SHOW TABLES already behaves a lot like SELECT    pdo   new PDO  mysql host  host dbname  dbname   user  pass   foreach   pdo- gt query  SHOW TABLES   as  row        print  Table  row Tables in  dbname  n

User · Answer

I think you want SELECT   FROM INFORMATION SCHEMA TABLES  See http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

Not that I know of  unless you select from INFORMATION SCHEMA  as others have mentioned    However  the SHOW command is pretty flexible   E g    SHOW tables like   s

User · Answer

in MySql 5 1 you can try   show tables like  user      output   mysql gt  show tables like  user      ----------------------------     Tables in test  user       ----------------------------     user                            user password                  ----------------------------   2 rows in set  0 00 sec

User · Answer

I think what you want is MySQL s information schema view s   http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

You can create a stored procedure and put the table names in a cursor  then loop through your table names to show the data    Getting started with stored procedure  http   www mysqltutorial org getting-started-with-mysql-stored-procedures aspx  Creating a cursor  http   www mysqltutorial org mysql-cursor   For example    CREATE PROCEDURE  ShowFromTables    BEGIN  DECLARE v finished INTEGER DEFAULT 0  DECLARE c table varchar 100  DEFAULT      DECLARE table cursor CURSOR FOR  SELECT table name FROM information schema tables WHERE table name like  wp 1     DECLARE CONTINUE HANDLER      FOR NOT FOUND SET v finished   1   OPEN table cursor   get data  LOOP  FETCH table cursor INTO c table   IF v finished   1 THEN  LEAVE get data  END IF   SET  s CONCAT  SELECT   FROM   c table        PREPARE stmt FROM  s  EXECUTE stmt  DEALLOCATE PREPARE stmt   END LOOP get data   CLOSE table cursor   END   Then call the stored procedure   CALL ShowFromTables

User · Answer

You may be closer than you think  mdash  SHOW TABLES already behaves a lot like SELECT    pdo   new PDO  mysql host  host dbname  dbname   user  pass   foreach   pdo- gt query  SHOW TABLES   as  row        print  Table  row Tables in  dbname  n

User · Answer

To count   SELECT COUNT    as total FROM  SELECT TABLE NAME as tab  TABLES   FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  database name  GROUP BY tab  tables    To list   SELECT TABLE NAME as table  TABLES   FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  database name  GROUP BY table

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES   That should be a good start  For more  check INFORMATION SCHEMA Tables

User · Answer

I think what you want is MySQL s information schema view s   http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

Have you looked into querying INFORMATION SCHEMA Tables   As in  SELECT ic Table Name      ic Column Name      ic data Type      IFNULL Character Maximum Length     AS  Max       ic Numeric precision as  Precision       ic numeric scale as Scale      ic Character Maximum Length as VarCharSize      ic is nullable as Nulls       ic ordinal position as OrdinalPos       ic column default as ColDefault       ku ordinal position as PK      kcu constraint name      kcu ordinal position      tc constraint type FROM INFORMATION SCHEMA COLUMNS ic     left outer join INFORMATION SCHEMA key column usage ku         on ku table name   ic table name         and ku column name   ic column name     left outer join information schema key column usage kcu         on kcu column name   ic column name         and kcu table name   ic table name     left outer join information schema table constraints tc         on kcu constraint name   tc constraint name order by ic table name  ic ordinal position

User · Answer

Yes  SELECT from table schema could be very usefull for system administration  If you have lot of servers  databases  tables    sometimes you need to DROP or UPDATE bunch of elements  For example to create query for DROP all tables with prefix name  wp old        SELECT concat  DROP TABLE    table name       FROM INFORMATION SCHEMA TABLES WHERE table schema     name of your database   AND table name LIKE  wp old

User · Answer

You can create a stored procedure and put the table names in a cursor  then loop through your table names to show the data    Getting started with stored procedure  http   www mysqltutorial org getting-started-with-mysql-stored-procedures aspx  Creating a cursor  http   www mysqltutorial org mysql-cursor   For example    CREATE PROCEDURE  ShowFromTables    BEGIN  DECLARE v finished INTEGER DEFAULT 0  DECLARE c table varchar 100  DEFAULT      DECLARE table cursor CURSOR FOR  SELECT table name FROM information schema tables WHERE table name like  wp 1     DECLARE CONTINUE HANDLER      FOR NOT FOUND SET v finished   1   OPEN table cursor   get data  LOOP  FETCH table cursor INTO c table   IF v finished   1 THEN  LEAVE get data  END IF   SET  s CONCAT  SELECT   FROM   c table        PREPARE stmt FROM  s  EXECUTE stmt  DEALLOCATE PREPARE stmt   END LOOP get data   CLOSE table cursor   END   Then call the stored procedure   CALL ShowFromTables

User · Answer

You can t put SHOW statements inside a subquery like in your example  The only statement that can go in a subquery is SELECT   As other answers have stated  you can query the INFORMATION SCHEMA directly with SELECT and get a lot more flexibility that way   MySQL s SHOW statements are internally just queries against the INFORMATION SCHEMA tables   User  physicalattraction has posted this comment on most other answers      This gives you  meta information about the tables  not the contents of the table  as the OP intended      physicalattraction   On the contrary  the OP s question does not say that they want to select the data in all the tables  They say they want to select from the result of SHOW TABLES  which is just a list of table names   If the OP does want to select all data from all tables  then the answer is no  you can t do it with one query  Each query must name its tables explicitly  You can t make a table name be a variable or the result of another part of the same query  Also  all rows of a given query result must have the same columns   So the only way to select all data from all tables would be to run SHOW TABLES and then for each table named in that result  run another query

User · Answer

I don t understand why you want to use  SELECT   FROM as part of the statement   12 5 5 30  SHOW TABLES Syntax

User · Answer

I think you want SELECT   FROM INFORMATION SCHEMA TABLES  See http   dev mysql com doc refman 5 0 en tables-table html

User · Answer

in MySql 5 1 you can try   show tables like  user      output   mysql gt  show tables like  user      ----------------------------     Tables in test  user       ----------------------------     user                            user password                  ----------------------------   2 rows in set  0 00 sec

User · Answer

Have you looked into querying INFORMATION SCHEMA Tables   As in  SELECT ic Table Name      ic Column Name      ic data Type      IFNULL Character Maximum Length     AS  Max       ic Numeric precision as  Precision       ic numeric scale as Scale      ic Character Maximum Length as VarCharSize      ic is nullable as Nulls       ic ordinal position as OrdinalPos       ic column default as ColDefault       ku ordinal position as PK      kcu constraint name      kcu ordinal position      tc constraint type FROM INFORMATION SCHEMA COLUMNS ic     left outer join INFORMATION SCHEMA key column usage ku         on ku table name   ic table name         and ku column name   ic column name     left outer join information schema key column usage kcu         on kcu column name   ic column name         and kcu table name   ic table name     left outer join information schema table constraints tc         on kcu constraint name   tc constraint name order by ic table name  ic ordinal position

User · Answer

Have you looked into querying INFORMATION SCHEMA Tables   As in  SELECT ic Table Name      ic Column Name      ic data Type      IFNULL Character Maximum Length     AS  Max       ic Numeric precision as  Precision       ic numeric scale as Scale      ic Character Maximum Length as VarCharSize      ic is nullable as Nulls       ic ordinal position as OrdinalPos       ic column default as ColDefault       ku ordinal position as PK      kcu constraint name      kcu ordinal position      tc constraint type FROM INFORMATION SCHEMA COLUMNS ic     left outer join INFORMATION SCHEMA key column usage ku         on ku table name   ic table name         and ku column name   ic column name     left outer join information schema key column usage kcu         on kcu column name   ic column name         and kcu table name   ic table name     left outer join information schema table constraints tc         on kcu constraint name   tc constraint name order by ic table name  ic ordinal position

User · Answer

Not that I know of  unless you select from INFORMATION SCHEMA  as others have mentioned    However  the SHOW command is pretty flexible   E g    SHOW tables like   s

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES   That should be a good start  For more  check INFORMATION SCHEMA Tables

User · Answer

Not that I know of  unless you select from INFORMATION SCHEMA  as others have mentioned    However  the SHOW command is pretty flexible   E g    SHOW tables like   s

User · Answer

Not that I know of  unless you select from INFORMATION SCHEMA  as others have mentioned    However  the SHOW command is pretty flexible   E g    SHOW tables like   s

User · Answer

Have you looked into querying INFORMATION SCHEMA Tables   As in  SELECT ic Table Name      ic Column Name      ic data Type      IFNULL Character Maximum Length     AS  Max       ic Numeric precision as  Precision       ic numeric scale as Scale      ic Character Maximum Length as VarCharSize      ic is nullable as Nulls       ic ordinal position as OrdinalPos       ic column default as ColDefault       ku ordinal position as PK      kcu constraint name      kcu ordinal position      tc constraint type FROM INFORMATION SCHEMA COLUMNS ic     left outer join INFORMATION SCHEMA key column usage ku         on ku table name   ic table name         and ku column name   ic column name     left outer join information schema key column usage kcu         on kcu column name   ic column name         and kcu table name   ic table name     left outer join information schema table constraints tc         on kcu constraint name   tc constraint name order by ic table name  ic ordinal position

User · Answer

SELECT column comment FROM information schema columns WHERE table name    myTable  AND column name    myColumnName    This will return the comment on  myTable myColumnName

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES   That should be a good start  For more  check INFORMATION SCHEMA Tables

User · Answer

To count   SELECT COUNT    as total FROM  SELECT TABLE NAME as tab  TABLES   FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  database name  GROUP BY tab  tables    To list   SELECT TABLE NAME as table  TABLES   FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA  database name  GROUP BY table

[mysql] Select data from "show tables" MySQL query

Examples related to mysql

Examples related to sql

Examples related to metadata