Search/sort Table Columns Names

[tfg]

I am working with some tables (an existing database) that have a very large number of column names. There are so many that it is difficult to find a desired column name because they are displayed “out of order” in the “manage columns” window (for example).

It would be very convenient if you could optionally select that the columns be displayed in alphabetical order or a “find” or “search” feature was available which would select the requested column name and scroll the window so that it was visible.

[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