How do I use `SHOW COLUMNS` as a valid data source

Question

I need to run the following query (this is a simplification of my process):

SELECT * 
FROM (SHOW COLUMNS FROM T1)

It errors out.

Answer 1

This is what you want to do:

select * from (
    select * from INFORMATION_SCHEMA.COLUMNS 
    where table_name = 'T1'
) dt

You cannot use SHOW COLUMNS in a subquery, but by using the INFORMATION_SCHEMA.COLUMNS table, you have much more information available, not only the column name, for instance.

Answer 2

See this post in SO Return order of MySQL SHOW COLUMNS

So probably you can use like as per the post

SELECT * FROM (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'tablename' 
 ORDER BY column_name) colinfo