How can I use SHOW CREATE TABLE in a subquery?

mysql, pdo, php, sql

Solution

Devart is correct that you can't join to the `SHOW CREATE` statement. However, depending on your exact needs, you can spoof this by creating your own `SHOW CREATE` statement.

The complexity of the code will increase if you need to include the database engine, column and table collations, indexes, and so forth - however the SQL below will give you the right table and fields, complete with datatypes. I'm sure you can extend it further by examining the contents of `information_schema.columns` in more depth.

SELECT CONCAT('CREATE TABLE `',t.TABLE_NAME,'` ',
  GROUP_CONCAT(CONCAT(c.COLUMN_NAME,' ',c.COLUMN_TYPE,' ',c.EXTRA) SEPARATOR ','),';') AS CreateStatement
FROM information_schema.tables t
INNER JOIN information_schema.columns c 
  ON t.TABLE_NAME=c.TABLE_NAME
/* WHERE STATEMENT IF NEEDED */;

Sample output:

CREATE TABLE `answers` rowid int(11) auto_increment,
  id int(11) ,username varchar(200) ,answer varchar(2000) ;

Problem

I'm trying to accomplish something like this: ``` SELECT * FROM information_schema.`tables` JOIN (SHOW CREATE TABLE) # <-- need help here WHERE table_schema LIKE 'tables\_%' ``` Is there a way to do this in one query?

Original source