Script to DROP CONSTRAINT and ADD CONSTRAINT for all CHECK CONSTRAINTS

16 September,2014 by Jack Vamvas

Question: I’m trying to a do a database collation change – and experiencing all sorts of issues. Including Msg 5075 - The database collation cannot be changed if a schema-bound object depends on it

Do you have a script where I can generate the DROP CONSTRAINT and ADD CONSTRAINT for all check  constraints ? The idea is to run the script before the ALTER DATABASE COLLATE statement , execute the DROP CONSTRAINT , make the database collation  change, and then execute the ADD CHECK CONSTRAINT script.

Answer:  This script will create DROP CONSTRAINT  and ADD CONSTRAINT statements. Ensure you execute both the statements below and save in a file.

Before you make any database changes , backup the database!

 

--ALTER TABLE DROP CONSTRAINT query creation

select 'ALTER TABLE '+QUOTENAME(object_schema_name(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id)) +' DROP CONSTRAINT '+QUOTENAME(sdc.name)+''
from sys.check_constraints sdc

 
--ALTER TABLE ADD CONSTRAINT query creation 
 
Select 'ALTER TABLE ‘+QUOTENAME(object_schema_name(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id))
 
+' ADD CONSTRAINT '+QUOTENAME(sdc.name)+' DEFAULT '+definition+' FOR '+QUOTENAME(c.name)+''
 
from sys.check_constraints sdc  
inner join sys.columns c on sdc.parent_object_id=c.object_id and sdc.parent_column_id=c.column_id

Read More

Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX

Modify a SQL CHECK CONSTRAINT - SQL Server

List Foreign Key Constraints -MS SQL - SQL Server DBA

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Script to DROP CONSTRAINT and ADD CONSTRAINT for all CHECK CONSTRAINTS


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer