Stored Procedure for Finding Columns in MySQL
Looking for instances particular column in a large schema can be a pain. Fortunately the information schema makes this pretty easy, if your columns have a consistent naming convention.
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%some_name%';
Now, if we want to wrap this up into an easy to use stored procedure, we can do something like this:
drop procedure find_column;
delimiter //
CREATE PROCEDURE find_column(c varchar(255))
begin
SET @a = CONCAT("%", c, "%");
SELECT table_schema, table_name, column_name, column_type
FROM information_schema.columns
WHERE column_name LIKE @a;
end
//
delimiter ;
We need to use the concat statement in order to properly get the quotes in there without using the literal string “c” in the LIKE statement.
You can do a search as follows:
CALL find_column("some_column");
Learn more on the MySQL Stored Procedures section of mysql.com
_Edit: added column type on suggested by Roland Bouman. _