How to select a column from the output of ‘SHOW COLUMNS’ sql

I am using the sql command SHOW COLUMNS in this way

SHOW COLUMNS FROM TABLE A;

It outputs multiple columns, specifically one called ‘COLUMN_NAME’ that I would like to select.

I tried doing

SELECT COLUMN_NAME FROM (SHOW COLUMNS FROM TABLE A);

which gives an error, is there another way I can just show one of the columns of the output? Is this because the output is not a table so I cannot query from it?

Answer

You can use the like function:

show columns like 'COLUMN_NAME' in table A;

There are limitations on using the output of the show command, so an alternate approach is to use the INFORMATION_SCHEMA.

select * from INFORMATION_SCHEMA.COLUMNS where 
    TABLE_SCHEMA = 'MY_SCHEMA' 
and TABLE_NAME = 'A' 
and COLUMN_NAME = 'COLUMN_NAME';

If you need to query the results of a show command, you can use the result_scan table function:

show columns like '%' in table t1;
select * from table(result_scan(last_query_id())) where "column_name" = 'COLUMN_NAME';

Remember to double quote the column names produced by the show command. It’s a metadata result and the column names are lower case.