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

Answer 3

I would like to refer a previous question, Because I have faced same problem and solved by this solution. First of all a constraint is always built with a Hash value in it's name. So problem is this HASH is varies in different Machine or Database. For example DF__Companies__IsGlo__6AB17FE4 here 6AB17FE4 is the hash value(8 bit). So I am referring a single script which will be fruitful to all

DECLARE @Command NVARCHAR(MAX)
     declare @table_name nvarchar(256)
     declare @col_name nvarchar(256)
     set @table_name = N'ProcedureAlerts'
     set @col_name = N'EmailSent'

     select @Command ='Alter Table dbo.ProcedureAlerts Drop Constraint [' + ( select 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 c.name = @col_name) + ']'

    --print @Command
    exec sp_executesql @Command

It will drop your default constraint. However if you want to create it again you can simply try this

ALTER TABLE [dbo].[ProcedureAlerts] ADD DEFAULT((0)) FOR [EmailSent]

Finally, just simply run a DROP command to drop the column.

Answer 4

I would like to offer this suggestion, as it requires only the table name and column name, and clearly indicates if it succeeded or failed, with error if necessary.

-- Removed unnamed default constraint from column if it exists.
-- If the default constraint doesn't exist, no error is raised

DECLARE @tablename AS varchar(128) = 'optional_schema.your_table'
DECLARE @columnname AS varchar(128) = 'your_column'
DECLARE @sql nvarchar(255) = NULL

select @sql = 'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + d.name + ';'
FROM sys.columns AS c
INNER JOIN sys.tables t on c.object_id = t.object_id AND t.object_id = OBJECT_ID(@tablename) 
INNER JOIN sys.default_constraints AS D ON c.default_object_id=d.object_id
WHERE c.default_object_id IS NOT NULL
AND c.name = @columnname

--SELECT isNull(@SQL, 'NOT DEFAULTED') AS [Statement] -- Only if you want to see the statement before it runs
IF (@SQL IS NULL) 
    PRINT ' Table ' + @tablename + ', column ' + @columnname + ' does not have a default constraint.'
ELSE
    BEGIN TRY
        EXECUTE sp_executesql @sql
        PRINT @sql + ' -- Successful'
    END TRY
    BEGIN CATCH
        PRINT @sql + ' -- Unsuccessful, Error: ' + ERROR_MESSAGE()
    END CATCH