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

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