-
Notifications
You must be signed in to change notification settings - Fork 1
/
fieldExistsLike.procedure.sql
32 lines (32 loc) · 1.26 KB
/
fieldExistsLike.procedure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- ------------------------------------------------------------
-- Use the information_schema to tell if a field exists.
-- Optional param dbName, defaults to current database
-- ------------------------------------------------------------
delimiter //
DROP PROCEDURE IF EXISTS `_fieldExists` //
CREATE PROCEDURE `_fieldExists` (
OUT _exists BOOLEAN, -- return value
IN columnName CHAR(255), -- name of column to look for
IN tableName CHAR(255), -- name of table to look for
IN dbName CHAR(255) -- optional specific db
) BEGIN
-- try current db if none provided
-- XXX inside a stored procedure, database() always resolves
-- to the db in which it's installed, so this feature is
-- useless unless it's installed on the db you would like
-- it to work on (read: WEAK).
SET @_dbName := IF(dbName IS NULL, database(), dbName);
SELECT @_dbName;
IF CHAR_LENGTH(@_dbName) = 0
THEN -- bail if no db to work with
SELECT FALSE INTO _exists;
ELSE -- we have a db to work with
SELECT IF(count(*) > 0, TRUE, FALSE) INTO _exists
FROM `information_schema`.`COLUMNS` c
WHERE
c.`TABLE_SCHEMA` LIKE CONCAT('%', @_dbName, '%')
AND c.`TABLE_NAME` LIKE CONCAT('%', tableName, '%')
AND c.`COLUMN_NAME` LIKE CONCAT('%', fieldName, '%')
END IF;
END //
delimiter ;