I'm trying to create upgrade and backout scripts in SQL. The upgrade script adds a column like so:
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'ColumnName'
AND object_id = OBJECT_ID(N'[dbo].[TableName]'))
ALTER TABLE TableName
ADD ColumnName bit NOT NULL DEFAULT(0)
The backout script removes the column like so:
IF EXISTS (SELECT * FROM sys.columns WHERE Name = N'ColumnName'
AND object_id = OBJECT_ID(N'[dbo].[TableName]'))
ALTER TABLE TableName
DROP COLUMN ColumnName
However, the backout script throws this error:
Msg 5074, Level 16, State 1, Line 5
The object 'DF__TableName__ColumnName__1BF3D5BD' is dependent on column 'ColumnName'.
Msg 4922, Level 16, State 9, Line 5
ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.
I know how to drop the constraint, but the constraint's name changes everytime (the suffix changes). I either need SQL Server to stop creating this randomly-named constraint OR I need to be able to remove the constraint in my script using wild-card characters, since the name changes.
Syntax: ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT; Example: Lets say we want to drop the constraint from STUDENTS table, which we have created in the above sections.
The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
This is the default
constraint that is added because of the DEFAULT(0)
in your newly added column.
You can name this yourself so it has a known fixed name rather than relying on the auto name generation.
ALTER TABLE TableName
ADD ColumnName bit NOT NULL CONSTRAINT DF_Some_Fixed_Name DEFAULT(0)
Then to remove the column and constraint together
ALTER TABLE dbo.TableName
DROP CONSTRAINT DF_Some_Fixed_Name, COLUMN ColumnName
Run this:
declare @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects
WHERE name LIKE 'DF__XXX__YYY__%' and type = 'D'
IF @name IS NOT NULL BEGIN
EXEC('ALTER TABLE XXX DROP CONSTRAINT ' + @name);
END
Run this if you want remove constraint:
DECLARE @tableName NVARCHAR(255) = '[INSERT]';
DECLARE @first5CharsFromColumnName NVARCHAR(255) = '[INSERT]';
DECLARE @name NVARCHAR(255);
SELECT @name = d.name FROM dbo.sysobjects d
INNER JOIN dbo.sysobjects t ON t.id = d.parent_obj
WHERE d.name LIKE '%'+@first5CharsFromColumnName+'%' AND d.type = 'D' AND t.name = @tableName
IF @name IS NOT NULL BEGIN
EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @name);
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With