Search/sort Table Columns Names

[peterlaursen]

This is a SQLyog question, not a MONyog question. Please post in the correct category. I will move you … 🙂

It is a basic consideration in the design of SQL-databases that such orders do not matter! However on the client side it is a reasonable idea! The problem is however that SQL syntax does not support it.

For instance: this SQL is used when you expand the columns folder in the Object Browser

Code:

show full fields from `database`.`table`

it would be nice (and supporting your request would be easy) if

Code:

show full fields from `database`.`table` order by `field`

.. worked. but ORDER BY is no supported with SHOW FULL FIELDS . The only way we/SQLyog (as any client) can communicate with the server is using SQL! Sorting on the client side like this is a major request – and I would think that this request should be sent to MySQL (to be implemented in the server) and not to us (to be implemented in a client)! SHOW DATABASES sort – SHOW TABLES and SHOW FULL FIELDs don't. This is a inconsistency in the MySQL Server, I think!

We are very careful NOT to (try to) implement 'server functionalities' in SQLyog.

On the other hand .. ok .. some sorting and/or filtering (even implemented on the client side) could be pretty useful, as long as the server does not.

We will need some time to consider!

[tfg]

How about using the following SQL statement to return the column names in order (try it with “table name” some valid table):

select column_name from information_schema.COLUMNS where table_name=”table name” order by column_name asc

I believe that information_schema is only available since version 5. Maybe this makes the use of the above unreasonable because your tool needs to be backwards compatible.

Server side sort/filter would be fine with me (it accomplished the desired goal just fine).

[peterlaursen]

The problems with queries to information_schema is that they can be very slow! And it can be a problem with shared hosting as a lot of ISPs have not yet understood, that users may query I_S!

You can read this discussion:

http://www.webyog.com/forums//index.php?sh…ormation_schema

However it is no problem to use different queries with different server versions. One of the first things we do after connection is to SELECT VERSION(). We do a lot of differentiation between 3.23 and 4.0/4.1 and higher allready!

I will not comment more on it tonight!

[Author]

Posts