Return order of MySQL SHOW COLUMNS

Question

Hey guys. Simple one this, but one I can't seem to find any information on so here goes.

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, preferable by choosing to order the results ascending alphabetically. I have had no luck with using ORDER BY Field.
Any ideas?

Cheers!

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