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

Sommer

Related, if it's of interest, I wrote a Describe All Tables in this Answer. You can change the order by by breaking out of their cmd and getting into this sort of flexible one. – Footloose

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.

Sippet

Answer 2

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;

Extortionate

FYI (perhaps obvious) to anybody who runs this: Make sure to replace 'table' with the name of the table you're wanting to describe, and 'database' with the name of the database it is in. – Correia

Answer 3

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

Spradlin

This is basically saying "google for schema information tables". A link, code, or anything specific to mysql would have been helpful. – Sagacity