Script to DROP CONSTRAINT and ADD CONSTRAINT for all CHECK CONSTRAINTS

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