5

I have a sizeable table that I'm actively working on and modifying columns of. It'd be handy if I could sort a describe table by Field name alphabetically to quickly find the definition for a column. The docs for mysql describe nor explain mention anything about sorting. How can I do this?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3791372
  • 4,445
  • 6
  • 44
  • 78

1 Answers1

5

DESCRIBE and SHOW COLUMNS don't have an option to sort their results. You can read more about them here: https://dev.mysql.com/doc/refman/5.7/en/show-columns.html

If you want a customized list of columns of a table, you can query the INFORMATION_SCHEMA.

SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='mydatabase' AND TABLE_NAME='mytable';

Read more about this table here: https://dev.mysql.com/doc/refman/5.7/en/columns-table.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • yep, querying the information_schema was going to be plan 'b', but it's sure as hell nasty! – user3791372 Oct 03 '17 at 17:57
  • 1
    An alternative is to run DESCRIBE, save it to a file, and run `sort` on the file. I use shell tools to fiddle with query output like that *constantly*. – Bill Karwin Oct 03 '17 at 17:58
  • interesting! i'll select this as the answer as it provides a (possibly, the) solution – user3791372 Oct 03 '17 at 18:00
  • I'm assuming you are on a Linux or Mac environment and have access to a decent shell and the `sort` command. If you're on Windows, you're on your own. :-) – Bill Karwin Oct 03 '17 at 18:01
  • 1
    yep, I couldn't imagine running mysql on a windows platform - even for dev - but KISS, I'll go with the information_schema table. Thanks! – user3791372 Oct 03 '17 at 18:43