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:
_Edit: added column type on suggested by Roland Bouman. _If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.