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

User · Accepted Answer

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

User · Answer

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  gt 0 EXEC sp executesql  sqlStatement

User · Answer

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

[sql] Drop default constraint on a column in TSQL

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql