Drop default constraint on a column in TSQL

Question

I have a table with a column like this that is currently live:

name NVARCHAR(128) NOT NULL DEFAULT '' 

I am altering the column like this 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. I know this could have been avoided if the original author named the constraint. I have several of instances of these tables but I don't want to manually delete every constraint in every table I have. What is the easiest and most elegant way of dropping this default constraint on a column in Sql Server that I can uniformily apply to every instance of this table?

EDIT

This is the script that I ended up using:

DECLARE @table_id AS INT DECLARE @name_column_id AS INT DECLARE @sql nvarchar(255)   -- Find table id SET @table_id = OBJECT_ID('mytable')  -- Find name column id SELECT @name_column_id = column_id FROM sys.columns WHERE object_id = @table_id AND name = 'name'  -- Remove default constraint from name column SELECT @sql = 'ALTER TABLE mytable DROP CONSTRAINT ' + D.name FROM sys.default_constraints AS D WHERE D.parent_object_id = @table_id AND D.parent_column_id = @name_column_id EXECUTE sp_executesql @sql 

Another script that can be used to accomplish this can be found here: How to drop SQL default constraint without knowing its name?

Thanks!

Answer 1

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

Answer 2

I would suggest:

DECLARE @sqlStatement nvarchar(MAX),         @tableName nvarchar(50) = 'TripEvent',         @columnName nvarchar(50) = 'CreatedDate';  SELECT                       @sqlStatement = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + dc.name + ';' FROM                 sys.default_constraints AS dc LEFT JOIN        sys.columns AS sc ON (dc.parent_column_id = sc.column_id) WHERE      dc.parent_object_id = OBJECT_ID(@tableName)     AND type_desc = 'DEFAULT_CONSTRAINT'     AND sc.name = @columnName  PRINT'   ['+@tableName+']:'+@@SERVERNAME+'.'+DB_NAME()+'@'+CONVERT(VarChar, GETDATE(), 127)+';  '+@sqlStatement;  IF (LEN(@sqlStatement) > 0)      EXEC sp_executesql @sqlStatement