1: -- ===========================================================
   2: -- Check Constraints
   3: -- How to script out Check Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- Drop Check Constraints
   8: SELECT
   9:     'ALTER TABLE  ' +
  10:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(CONSTRAINT_NAME)
  14: FROM
  15:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  16:     INNER JOIN sys.sysobjects so
  17:     ON cc.CONSTRAINT_NAME = so.[name]
  18:
  19: -- Recreate Check Constraints
  20: SELECT
  21:     'ALTER TABLE  ' +
  22:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  23:     CHAR(10) +
  24:     ' ADD CONSTRAINT ' +
  25:     QuoteName(CONSTRAINT_NAME) +
  26:     ' CHECK ' +
  27:     CHECK_CLAUSE
  28: FROM
  29:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  30:     INNER JOIN sys.sysobjects so
  31:     ON cc.CONSTRAINT_NAME = so.[name]

Here is a sample result:

ALTER TABLE  [ProductReview]
 DROP CONSTRAINT [CK_ProductReview_Rating]
ALTER TABLE  [ProductReview]
 ADD CONSTRAINT [CK_ProductReview_Rating] CHECK ([Rating]>=(1) AND [Rating]<=(5))
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
Script to Drop/Recreate CHECK Constraints, 10.0 out of 10 based on 1 rating  
Be Sociable, Share!
  • Tweet

Related posts:

  1. Script to Drop/Recreate Default Constraints …
  2. Script to Drop/Recreate Unique Indexes …
  3. How to Drop All Stored Procedures in Your Database …
  4. SQLXML : How to Check if an XML Schema Already Exists …
  5. Drop and Re-Add Server Registration When Renaming Computer …
  6. Powershell Script to Add ActiveDirectory (AD) Users …

YARPP