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 · 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.

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

This is how you would drop the constraint  ALTER TABLE  lt schema name  sysname  dbo gt   lt table name  sysname  table name gt     DROP CONSTRAINT  lt default constraint name  sysname  default constraint name gt  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

[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