Ordering field names in a "DESC / DESCRIBE table" or "SHOW COLUMNS

Hi all,

I have a Linux server with MySQL 5.0 (5.0.67) installed.

In that server, I have a database that has a table with many fields (around 60).

It would be convenient for me to get a list of those fields ordered by
field / column name. Unfortunately, a "DESC tablename ("DESCRIBE
tablename") command, in "MySQL monitor" (the MySQL command line
client) returns a table with that table fields, but the fields appear
in a strange order. I'm guessing this strange order can be the order
given by the "CREATE TABLE" command that was used to create that
table.

The first thing I tried was to write "DESC tablename ORDER BY Field".
That failed:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'ORDER BY Field' at line 1


So, I went to read the online documentation about the DESCRIBE command
and the SHOW COLUMNS (or its synonym SHOW FIELDS) command:

MySQL :: MySQL 5.0 Reference Manual :: 12.3.1 DESCRIBE Syntax
http://dev.mysql.com/doc/refman/5.0/en/describe.html

MySQL :: MySQL 5.0 Reference Manual :: 12.5.5.5 SHOW COLUMNS Syntax
http://dev.mysql.com/doc/refman/5.0/en/show-columns.html


Unfortunately, I'm NOT finding, in those 2 web pages, any hints about
sorting the list of fields by any criteria.

I did some "google" searches about this, but I didn't find any
relevant results (maybe I'm using the wrong keywords for my searches).

So, I'm asking this: does anyone know a MySQL command to get this list
of fields sorted by field name? Or do you achieve this result by other
means (e.g: using some GUI tool, copying the list of fields to a text
file and sorting it, writing some script...)?


Thanks in advance!

Best wishes,
Ricardo Dias Marques
lists AT ricmarques DOT net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Ordering field names in a "DESC / DESCRIBE table" or "SHOW COLUMNS from table&quo

--------------040000030800050709050100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

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

PB

-----

Ricardo Dias Marques wrote:
> Hi all,
>
> I have a Linux server with MySQL 5.0 (5.0.67) installed.
>
> In that server, I have a database that has a table with many fields (around 60).
>
> It would be convenient for me to get a list of those fields ordered by
> field / column name. Unfortunately, a "DESC tablename ("DESCRIBE
> tablename") command, in "MySQL monitor" (the MySQL command line
> client) returns a table with that table fields, but the fields appear
> in a strange order. I'm guessing this strange order can be the order
> given by the "CREATE TABLE" command that was used to create that
> table.
>
> The first thing I tried was to write "DESC tablename ORDER BY Field".
> That failed:
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near 'ORDER BY Field' at line 1
>
>
> So, I went to read the online documentation about the DESCRIBE command
> and the SHOW COLUMNS (or its synonym SHOW FIELDS) command:
>
> MySQL :: MySQL 5.0 Reference Manual :: 12.3.1 DESCRIBE Syntax
> http://dev.mysql.com/doc/refman/5.0/en/describe.html
>
> MySQL :: MySQL 5.0 Reference Manual :: 12.5.5.5 SHOW COLUMNS Syntax
> http://dev.mysql.com/doc/refman/5.0/en/show-columns.html
>
>
> Unfortunately, I'm NOT finding, in those 2 web pages, any hints about
> sorting the list of fields by any criteria.
>
> I did some "google" searches about this, but I didn't find any
> relevant results (maybe I'm using the wrong keywords for my searches).
>
> So, I'm asking this: does anyone know a MySQL command to get this list
> of fields sorted by field name? Or do you achieve this result by other
> means (e.g: using some GUI tool, copying the list of fields to a text
> file and sorting it, writing some script...)?
>
>
> Thanks in advance!
>
> Best wishes,
> Ricardo Dias Marques
> lists AT ricmarques DOT net
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.432 / Virus Database: 270.14.138/2618 - Release Date: 01/13/10 07:35:00
>
>

--------------040000030800050709050100--

Re: Ordering field names in a "DESC / DESCRIBE table" or "SHOW

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
kindly replied:

> SELECT *
> FROM information_schema.columns
> WHERE table_schema=3D'db' AND table_name=3D'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=3D'database_name' AND table_name=3D'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=E0"!

Thanks again for helping me Peter! :)

Cheers,
Ricardo Dias Marques
lists AT ricmarques DOT net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg