CODEWITHSUNDEEP
sqlsql-server
Alexander
Alexander

Reputation: 20224

Reliably remove default constraint from column

The following code I found somewhere on SO, and slightly modified the variable names.

-- If data type differs, drop constraints and change data type.
WHILE 0=0
BEGIN
    SET @ConstraintName = (SELECT TOP 1 constraint_name FROM information_schema.constraint_column_usage WHERE table_name = @TableName and column_name = @FieldName)
    IF @ConstraintName IS NULL BREAK;
    EXEC('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT "' + @ConstraintName + '"');
END
EXEC('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @FieldName + ' ' + @FieldType + ' NOT NULL');

Albeit dropping all constraints found in information schema, the subsequent execution still throws the error:

The object 'DF_settings_monitoring' is dependent on column 'monitoring'.
ALTER TABLE ALTER COLUMN monitoring failed because one or more objects access this column.

in some cases. Only some, not all.

I have found that the information_schema.constraint_column_usage table does not contain all default constraints, but I am unsure why.

Is there a more reliable source of information about existing constraint on a certain column?

Upvotes: 0

Views: 60

Answers (1)

JAQFrost
JAQFrost

Reputation: 1431

As answered here, information_schema doesn't include default constraints.

So if you replace your select with this:

select top 1 name from sys.default_constraints

... You should be good to go.

Upvotes: 1

Related Questions