How to SHOW COLUMNS from a SELECT query (rather than a table)?

Question

I get a syntax error when I run the following:

show columns from (select * from (select * from my_table) as T)

How can I show the columns from a query that I wrote, rather than from a table?

Answer 1

METHOD 1: Temporary table

The answers already posted about using a temporary table will usually be the most appropriate solution. But there is an important point that if the query is run as-is, all the joins etc. will be processed, which could potentially take a long time in some cases. Fortunately, MySQL allows LIMIT 0 to return no rows and the documentation states that this "quickly returns an empty set". The following stored procedure will do this job for you by taking a SQL query string as input, wrapping it with LIMIT 0, running the dynamic query to produce a temporary table and then showing its columns:

CREATE PROCEDURE showColumns(IN sqlToShow TEXT)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tempTable;
    SET @sqlLimit0 = CONCAT('CREATE TEMPORARY TABLE tempTable AS (SELECT * FROM (',
                            sqlToShow, ') subq LIMIT 0)');
    PREPARE stmt FROM @sqlLimit0;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SHOW COLUMNS FROM tempTable;
END;

One important point is the query passed in should not have a semi-colon at the end. (If necessary, the stored procedure could be modified to remove trailing semi-colons but I wanted to keep it simple.)

Here is a live demo showing it in action: http://rextester.com/NVWY58430

METHOD 2: INFORMATION_SCHEMA.COLUMNS

The same information returned by SHOW COLUMNS can also be obtained directly from the INFORMATION_SCHEMA.COLUMNS table:

SELECT TABLE_NAME AS `Table`,
       COLUMN_NAME AS `Field`,
       COLUMN_TYPE AS `Type`,
       IS_NULLABLE AS `Null`,
       COLUMN_KEY AS `Key`, 
       COLUMN_DEFAULT AS `Default`,
       EXTRA AS `Extra`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = SCHEMA() -- This uses the current schema
  AND `TABLE_NAME` IN ('table1', 'table2', 'etc.');
   -- ...or could go even further and restrict to particular columns in tables if desired

The above suffers from the disadvantages of requiring the table (and optionally column) names to be entered manually and not showing alias names in the SELECT but it does the basic job. Its advantages are it doesn't require the user to have permission to create a temporary table and the information returned could be extended to provide further column information such as maximum character length, numeric precision/scale, column comments etc.