[SOLVED] Drop default constraint on a column in TSQL

Drop default constraint on a column in TSQL

I have a table with a column:

name NVARCHAR(128) NOT NULL DEFAULT ''

I am altering the column to make it nullable:

ALTER TABLE  mytable ALTER COLUMN name NVARCHAR(128) NULL

However the default constraint named DF__mytable__datab__7DE4B36 in one instance of the table, still remains. This could have been avoided if I named the constraint. I don't want to manually delete every constraint in every table I have. What way of dropping this default constraint on a column in SQL Server can I uniformly apply to every instance of this table?

Solution

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