How can I use SHOW CREATE TABLE in a subquery?

Question

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?

Answer 1

There is no ways to do it in this way. The SHOW CREATE TABLE command retrieves a data-set, but the result cannot be joined with another table. Execute SHOW CREATE TABLE as a separate command for each table you need in the application.

Answer 2

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