xxxxxxxxxx
DELIMITER //
CREATE PROCEDURE AddColumnIfNotExists()
BEGIN
DECLARE CONTINUE HANDLER FOR 1060 -- Error code for duplicate column name
BEGIN
-- Column already exists; do nothing
END;
-- Check if the column exists
SELECT COUNT(*)
INTO @column_exists
FROM information_schema.columns
WHERE table_name = 'enter-name-here' AND column_name = 'enter-name-here';
-- If the column does not exist, add it
IF @column_exists = 0 THEN
ALTER TABLE `enter-database-name`.`enter-table-name`
ADD COLUMN `enter-column-name` VARCHAR(255) NULL DEFAULT NULL AFTER `enter-column-name`;
END IF;
END;
//
xxxxxxxxxx
-- Checking if the column already exists
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'
AND COLUMN_NAME = 'your_column_name'
)
BEGIN
-- Adding the column
ALTER TABLE your_table_name
ADD your_column_name datatype;
END;
xxxxxxxxxx
IF NOT EXISTS (
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'table_name' AND COLUMN_NAME = 'col_name')
BEGIN
ALTER TABLE table_name
ADD col_name data_type NULL
END;
xxxxxxxxxx
IF COL_LENGTH('table_name','column_name') IS NOT NULL
PRINT 'Column Exists';
ELSE
PRINT 'Column does not Exists';
xxxxxxxxxx
SELECT
IF(count(*) = 1, 'Exist','Not Exist') AS result
FROM
information_schema.columns
WHERE
table_schema = 'classicmodels'
AND table_name = 'vendors'
AND column_name = 'phone';
Code language: SQL (Structured Query Language) (sql)