Stored Procedure For Finding Columns In MySQL
•
1 min read
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.
mysql
stored procedure
Read more