How to find Specific Column Names or Types in a mySQL Database
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:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE lower(column_name) LIKE '%sort%'
AND 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:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='//my database name here//'
or all columns with a specific data type
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='//my database name here//'
AND 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:
alter table #table_Name# modify #column_name# VARCHAR(55) ;
(this would set the size of your varchar field to 55 characters, for example)
