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

mysqlstored procedure