I'm working on a rather extensive CMS admin application, and need to make some adjustments to all of the "sort" fields in the database. Many of the tables have either a 'sort' or 'sort_order' (and in one case, I think I saw 'SortOrder'), and I need hunt them down, creating a list of column names and table names so i can quickly find the columns I want to edit.

I could open each table one by one, and look for those column names... but of course, there's a much better way:

view plain print about
1SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
2FROM INFORMATION_SCHEMA.COLUMNS
3WHERE lower(column_name) LIKE '%sort%'
4AND TABLE_SCHEMA='//my database name here//'

This returns a perfectly formatted query of table names and column names, along with the 'data type' for any column containing 'sort' (or 'Sort'). Now it will be a snap to make my changes to all of the numeric sort fields in the entire database.

The query can be modified to return all sorts of useful info, such as all columns in the entire database:

view plain print about
1SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
2FROM INFORMATION_SCHEMA.COLUMNS
3WHERE TABLE_SCHEMA='//my database name here//'

or all columns with a specific data type

view plain print about
1SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
2FROM INFORMATION_SCHEMA.COLUMNS
3WHERE TABLE_SCHEMA='//my database name here//'
4AND DATA_TYPE='varchar'

And then, if you wanted to get really lazy, er, creative... you can loop the results of that query, and 'do stuff' to all of the columns automatically with just a few more lines:

view plain print about
1alter table #table_Name# modify #column_name# VARCHAR(55) ;

(this would set the size of your varchar field to 55 characters, for example)