What is the cleanest way to sort "describe table" query results?

Question

I'm working on "describe table" output to show a list of fields and their types, i want my primary keys to be at top of the list.. I think there's no way to sort describe's results using SQL (something like 'order by') rather than sorting it in PHP.

what do you think guys ? thanks

Answer 1

When you do a SHOW COLUMNS or a DESCRIBE TABLE, you're really just using the builtin special database called INFORMATION_SCHEMA to pull information about a named table. Funny thing is, it seems to not return the information as a table, so it's impossible to get the data returned by those functions to act like a table (for sorting, subquerying, etc.).

Fortunately, you can set up your own query to perform the same lookup as SHOW or DESCRIBE:

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 = 'my table' and
    TABLE_SCHEMA = 'my database'

-- add ordering --
order by COLUMN_TYPE;