Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database

Script to extract all check constraints from a database

0.00/5 (No votes)
24 May 2011CPOL 13.3K  
From a utility I'm working on. Compatible with SQL-92 INFORMATION_SCHEMA views.

This is from a utility I'm working on. It is compatible with SQL-92 INFORMATION_SCHEMA views. Enjoy!

SQL
DECLARE @constraints CURSOR;
DECLARE
        @table_full_name       nvarchar(max)
,       @constraint_name       nvarchar(max)
,       @constraint_full_name  nvarchar(max)
,       @constraint_definition nvarchar(max)
;

SET @constraints = CURSOR FOR
SELECT
        QUOTENAME(cc.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(cc.CONSTRAINT_NAME) _
       AS constraint_full_name
,       QUOTENAME(cc.CONSTRAINT_NAME)                                         _
       AS constraint_name
,       QUOTENAME(ctu.TABLE_SCHEMA) + '.' + QUOTENAME(ctu.TABLE_NAME)         _
       AS table_full_name
,       cc.CHECK_CLAUSE                                                       _
       AS constraint_definition
FROM
       INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
       INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu
           ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
ORDER BY
        cc.CONSTRAINT_SCHEMA
,       cc.CONSTRAINT_NAME

OPEN @constraints
FETCH NEXT FROM @constraints INTO
       @constraint_full_name,
       @constraint_name,
       @table_full_name,
       @constraint_definition;

WHILE @@FETCH_STATUS = 0 BEGIN
        PRINT   ' '
        PRINT   'IF EXISTS (SELECT * FROM sys.check_constraints '
        PRINT   '                       WHERE object_id       = OBJECT_ID(N' + _
                                        CHAR(39) + @constraint_full_name + CHAR(39) + ')'
        PRINT   '                       AND parent_object_id  = OBJECT_ID(N' + _
                                        CHAR(39) + @table_full_name + CHAR(39) + ')) '
        PRINT   '       ALTER TABLE ' + @table_full_name + ' DROP CONSTRAINT ' + _
                '                       @constraint_name
        PRINT   'GO'
        PRINT   ' '
        PRINT   'ALTER TABLE '     + @table_full_name + ' WITH NOCHECK '
        PRINT   '       ADD CONSTRAINT '   + @constraint_name
        PRINT   '       CHECK '        + @constraint_definition
        PRINT   'GO'
        PRINT   ' '
        PRINT   'ALTER TABLE ' + @table_full_name + ' CHECK CONSTRAINT ' + _
                   @constraint_name
        PRINT   'GO'
        PRINT   ' '
        FETCH NEXT FROM @constraints INTO
                   @constraint_full_name,
               @constraint_name,
               @table_full_name,
               @constraint_definition;
END
CLOSE              @constraints;
DEALLOCATE         @constraints;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)