Poking Around in MySql

One of the tools that I have come to love is RedGate’s SQL Search. If you’re not using an ORM, or if you need to get down into SQL Server and find columns, procedures or otherwise, it becomes indispensable to quickly get a handle on a largish database.

In my latest project, we made the decision to move to MySql, due in large part to SQL Server’s ballooning cost structure. After a few months of working with MySql, I feel qualified to say that in some ways, you get what you pay for. We haven’t found the tools around MySql to be anywhere near the polish of some of the SQL Server tools (the SQL Server installer itself, not included of course).

In order to find things in our database I did some research on rolling my own Sql Search, what follows are some of the things I have found.

If you want to find a column used in a function:

SELECT Name, CAST(body AS CHAR(10000) CHARACTER SET utf8) 
   FROM mysql.proc
   WHERE body LIKE '%Search term%'
      AND db = 'DB Name';
If you wanted to quickly glance at the whole of the procedure, get rid of those pesky line breaks:
SELECT Name, REPLACE(CAST(body AS CHAR(10000) CHARACTER SET utf8),'\r\n',' ')
   FROM mysql.proc
   WHERE body LIKE '%Search term%'
      AND db = 'DB Name';

If we’re looking for a particular column name:

SELECT * 
   FROM INFORMATION_SCHEMA.Columns
   WHERE table_schema = 'DB Name'
      AND column_name = 'Column Name';

There is a very annoying .NET to MySql bug in that if you have anything but NULL or a 36 character string in any field with a column type and size of CHAR(36), MySql throws an exception. If you have someone testing and they put incorrect data in these columns, no one can select until the data is corrected. Want to find all your potential problem columns then?

SELECT Table_Name, Column_Name
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE DATA_TYPE = 'CHAR'
      AND CHARACTER_MAXIMUM_LENGTH = 36 
      AND TABLE_SCHEMA = 'DB Name';

I’m getting more comfortable with MySql now that I know where to look for things. If you’re using MySql, I hope this helps.