Here is a basic script to drop and recreate default constraints in SQL Server.
   1: -- ===========================================================
   2: -- Default Constraints
   3: -- How to script out Default Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- drop default constraints
   8: SELECT
   9:     'ALTER TABLE ' +
  10:     QuoteName(OBJECT_NAME(sc.id)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(OBJECT_NAME(sc.cdefault))
  14: FROM
  15:     syscolumns sc
  16:     INNER JOIN
  17:     sysobjects as so on sc.cdefault = so.id
  18:     INNER JOIN
  19:     syscomments as sm on sc.cdefault = sm.id
  20: WHERE
  21:     OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1
  22:
  23: -- create default constraints
  24: SELECT
  25:     'ALTER TABLE ' +
  26:     QuoteName(OBJECT_NAME(sc.id)) +
  27:     'WITH NOCHECK ADD CONSTRAINT ' +
  28:     QuoteName(OBJECT_NAME(sc.cdefault))+
  29:     ' DEFAULT ' +
  30:     sm.text +
  31:     ' FOR ' + QuoteName(sc.name)
  32:     + CHAR(13)+CHAR(10)
  33: FROM
  34:     syscolumns sc
  35:     INNER JOIN
  36:     sysobjects as so on sc.cdefault = so.id
  37:     INNER JOIN
  38:     syscomments as sm on sc.cdefault = sm.id
  39: WHERE
  40:     OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1
Here is a sample result:
ALTER TABLE [SalesOrderHeader]
 DROP CONSTRAINT [DF_SalesOrderHeader_Status]
ALTER TABLE [PurchaseOrderHeader] WITH NOCHECK
 ADD CONSTRAINT [DF_PurchaseOrderHeader_Status] DEFAULT ((1)) FOR [Status]
VN:F [1.9.22_1171]
Rating: 9.7/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)
Script to Drop/Recreate Default Constraints, 9.7 out of 10 based on 6 ratings  
Be Sociable, Share!
  • Tweet

Related posts:

  1. Script to Drop/Recreate CHECK Constraints …
  2. Script to Drop/Recreate Unique Indexes …
  3. How to Drop All Stored Procedures in Your Database …
  4. Drop and Re-Add Server Registration When Renaming Computer …
  5. PowerShell Goodies: Ebooks, CheatSheets, Script Repository …
  6. Powershell Script to Add ActiveDirectory (AD) Users …

YARPP