Drop Default Constraint on a Column in Tsql

Drop default constraint on a column in TSQL

This is how you would drop the constraint

ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, table_name>
   DROP CONSTRAINT <default_constraint_name, sysname, default_constraint_name>
GO

With a script

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

Credits go to Jon Galloway
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

How can I delete or alter a default constraint from table in SQL Server database for multiple database?

Run this and output as text, then copy the result and run in another query window

Fritz with the code below to ADD the updated constraint:

;With cte As (select object_id From sys.objects where name like 'DF__Documents__ShowO%')
Select 'Alter Table ' + Object_Name(df.object_id) + N' Add Constraint Default (1) For ShowOnHandset'
From sys.default_constraints As df
Join cte As c
On c.object_id = df.object_id

This deletes the constraints

;With cte As (select object_id From sys.objects where name like 'DF__Documents__ShowO%')
Select 'Alter Table ' + Object_Name(df.object_id) + N' Drop Constraint [' + df.Name + ']'
From sys.default_constraints As df
Join cte As c
On c.object_id = df.object_id

How to drop SQL default constraint without knowing its name?

Expanding on Mitch Wheat's code, the following script will generate the command to drop the constraint and dynamically execute it.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

How to drop column with constraint?

First you should drop the problematic DEFAULT constraint, after that you can drop the column

alter table tbloffers drop constraint [ConstraintName]
go

alter table tbloffers drop column checkin

But the error may appear from other reasons - for example the user defined function or view with SCHEMABINDING option set for them.

UPD:
Completely automated dropping of constraints script:

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

---

Related Topics