Stored Procedure for Finding Columns in MySQL

  • Thu, Dec 10, 2009
  • One-minute read

sakila.png

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. _

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.
mysqlstored procedure