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.

Answer 2

I am using Java to retrieve columns from MySql query.

The best way in Java to get column information for a result set is to use the ResultSetMetaData interface:

PreparedStatement stmt = null;
ResultSet result = null;
ResultSetMetaData meta = null;

try {
    stmt = con.prepareStatement("SELECT * FROM MyTable"); 
    result = stmt.executeQuery();
} catch (SQLException e) {
    System.out.println("SQLException: "+e.getMessage());
    System.exit(1);
}
System.out.println("Successful query");

try {
    meta = result.getMetaData();
    System.out.println("Total columns: " + meta.getColumnCount());
    System.out.println("Name of column 1: " + meta.getColumnName(1));
    System.out.println("Type of column 1: " + meta.getColumnTypeName(1));

    System.out.println("Name of column 2: " + meta.getColumnName(2));
    System.out.println("Type of column 2: " + meta.getColumnTypeName(2));
} catch (SQLException e) {
    System.out.println("SQLException: "+e.getMessage());
    System.exit(1);
}
System.out.println("Successful metadata report");

My table is declared:

CREATE TABLE `MyTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Output of my example Java code:

Successful query
Total columns: 2
Name of column 1: id
Type of column 1: BIGINT UNSIGNED
Name of column 2: name
Type of column 2: VARCHAR
Successful metadata report

You can get other information about result set columns besides their names and data types. See http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html for full reference docs on the ResultSetMetaData interface.