When we try to delete a column with a constraint, we need to delete the constraint first. But if the constraint is created by the db system, the constraint name will have a random hexadecimal suffix. Therefore, we should get the constraint name first. We can use the following SQL to get the constraints details.
SELECT DEFAULT_CONSTRAINTS.NAME
FROM SYS.ALL_COLUMNS, SYS.TABLES, SYS.DEFAULT_CONSTRAINTS
WHERE ALL_COLUMNS.OBJECT_ID = TABLES.OBJECT_ID
AND ALL_COLUMNS.DEFAULT_OBJECT_ID = DEFAULT_CONSTRAINTS.OBJECT_ID
query result
Therefore, we can use this way to get the name of the constraint which is related to the target column first. Then save it as a variable, and delete the constraint and the column.
The complete codes are as follows:
IF COL_LENGTH('TABLE_NAME', 'COLUMN_NAME') IS NOT NULL
BEGIN
DECLARE @CONSTR_NAME VARCHAR(MAX)
SET @CONSTR_NAME = (
SELECT DEFAULT_CONSTRAINTS.NAME
FROM SYS.ALL_COLUMNS, SYS.TABLES, SYS.DEFAULT_CONSTRAINTS
WHERE ALL_COLUMNS.OBJECT_ID = TABLES.OBJECT_ID
AND ALL_COLUMNS.DEFAULT_OBJECT_ID = DEFAULT_CONSTRAINTS.OBJECT_ID AND TABLES.NAME = 'TABLE_NAME'
AND ALL_COLUMNS.NAME = 'COLUMN_NAME')
IF @CONSTR_NAME IS NOT NULL
BEGIN
DECLARE @STATEMENT VARCHAR(MAX)
SET @STATEMENT = 'ALTER TABLE TABLE_NAME DROP ' + @CONSTR_NAME
EXEC (@STATEMENT)
END
ALTER TABLE Scripts
DROP COLUMN COLUMN_NAME
END
GO
