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))
1 Comment
Filed under:
DBA Toolbox / T-SQL Scripts