List:General Discussion« Previous MessageNext Message »
From:Ricardo Dias Marques Date:January 14 2010 7:55pm
Subject:Re: Ordering field names in a "DESC / DESCRIBE table" or "SHOW
COLUMNS from table" command
View as plain text  
Hi Peter (and MySQL list),


On Wed, Jan 13, 2010 I (Ricardo Dias Marques) asked the following :

>>It would be convenient for me to get a list of those fields ordered by
>>field / column name.


... and on the same day, Peter Brawley <peter.brawley@stripped>
kindly replied:

> SELECT *
> FROM information_schema.columns
> WHERE table_schema='db' AND table_name='tbl';

Thank you Peter! With your good example, it became very easy for me to
reach a working solution, that was running the following query
(replacing "database_name" and "table_name" by their real names,
obviously):

SELECT column_name from INFORMATION_SCHEMA.columns WHERE
table_schema='database_name' AND table_name='table_name' ORDER BY
column_name;


This query works perfectly in the scenario that I described in my
original post (Linux server running MySQL 5.0). It does NOT work for
MySQL 4.x, however (I have another machine that only has MySQL
4.1.12).

For MySQL 4, the best I could do was this:

1 - Run the following "mysqlshow" command :

# mysqlshow database_name table_name -p > field_list.txt


2 - Open the "field_list.txt" in the Vim text editor. I then did a
column selection in that file (by pressing CTRL + V to enter "Visual
Block" mode and then selecting and deleting), I removed the columns
that I didn't need (basically, removed every column EXCEPT the "Field"
column).
I also removed the extra lines that the "mysqlshow" command adds
(decorative lines, column labels, etc...). Then, I saved this changed
text file.


3 - Finally, I ran the "sort" command on that file:

# sort field_list.txt


"Et voilà"!

Thanks again for helping me Peter!  :)

Cheers,
Ricardo Dias Marques
lists AT ricmarques DOT net
Thread
Ordering field names in a "DESC / DESCRIBE table" or "SHOW COLUMNS from table" commandRicardo Dias Marques13 Jan
  • Re: Ordering field names in a "DESC / DESCRIBE table" or "SHOW COLUMNSfrom table" commandPeter Brawley13 Jan
    • Re: Ordering field names in a "DESC / DESCRIBE table" or "SHOW COLUMNS from table" commandRicardo Dias Marques14 Jan