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

Question

Hello everyone,

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

You're correct. MySQL will always output columns in their actual order — that is, the order they're physically stored in the table's data.

You can physically move columns around within your table so that the primary keys are first, though this will require MySQL to lock the table and rewrite its entire data in the new order. That's almost never worthwhile.

If you're just interested in presenting nice output, regardless of how the table is actually structured, you can indeed sort the columns in PHP, perhaps using a simple usort() call.

Answer 2

Do not use describe but use the schema informationen tables which are proived by most dbms. Then you can use simple selects to get your information.

Answer 3

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

    -- add ordering --
    order by Type;

Aside to OP: Thanks for giving me incentive to look up how to do this again. I once knew how to do this but had forgotten.