Return order of MySQL SHOW COLUMNS

Question

I need to find the columns in a specific table, which is no problem:

SHOW COLUMNS FROM tablename LIKE '%ColumnPrefix%';

But I need to know what order they will be returned, preferably by choosing to order the results ascending alphabetically. I have had no luck with using ORDER BY.

Any ideas?

Answer 1

You can query the table INFORMATION_SCHEMA.COLUMNS to get the information that SHOW COLUMNS gives you, plus it allows you to use ORDER BY or any other SQL syntax you might want to use:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tablename'
  AND column_name LIKE 'ColumnPrefix%'
ORDER BY column_name

Answer 2

Since I have had the exact same problem I will complete Mark's answer. Here is the exact long version of the 'show columns from table' query:

SELECT 
    `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_name`='mytable';

With this query you can order the result however you wish by adding ORDER BY.