SqlServer: Generate alter add / drop for all type of constraints (foreign key, unique and check)

generate_add_and_drop_all_constraints.sql

with
	unique_constraint_infos (schemaname, tablename, constraintname, columnname)
	as (
		select
			quotename(tc.table_schema)
			, quotename(tc.table_name)
			, quotename(tc.constraint_name)
			, quotename(cc.column_name)
		from
			information_schema.table_constraints tc
			inner join information_schema.constraint_column_usage cc on tc.constraint_name = cc.constraint_name
		where
			lower(tc.constraint_type) = 'unique'
	)
	, check_constraint_infos (schemaname, tablename, constraintname, definition)
	as (
		select
			quotename(cs.name)
			, quotename(ct.name)
			, quotename(ck.name)
			, ck.definition 
		from
			sys.check_constraints ck
			inner join sys.tables ct on ck.parent_object_id = ct.[object_id]
			inner join sys.schemas cs on ct.[schema_id] = cs.[schema_id]	
	)
	, foreign_key_infos (constraintschemaname, constrainttablename, referenceschemaname, referencetablename, constraintname, constraintcolumns, referencecolumns)
	as (
		select
			quotename(cs.name)
			, quotename(ct.name)
			, quotename(rs.name)
			, quotename(rt.name)
			, quotename(fk.name)
			, stuff(
				(select 
					',' + quotename(c.name)
					-- get all the columns in the constraint table
				from
					sys.columns as c 
				inner join sys.foreign_key_columns as fkc 
					on fkc.parent_column_id = c.column_id
					and fkc.parent_object_id = c.[object_id]
				where
					fkc.constraint_object_id = fk.[object_id]
				for xml path(''), type
				).value('.[1]', 'nvarchar(max)')
			, 1, 1, ''
			)
			, stuff(
				(select 
					',' + quotename(c.name)
					-- get all the referenced columns
				from 
					sys.columns as c 
					inner join sys.foreign_key_columns as fkc 
						on fkc.referenced_column_id = c.column_id
						and fkc.referenced_object_id = c.[object_id]
					where fkc.constraint_object_id = fk.[object_id]
					for xml path(''), type
					).value('.[1]', N'nvarchar(max)')
			, 1, 1, '')
		from
			sys.foreign_keys as fk
			inner join sys.tables as rt on fk.referenced_object_id = rt.[object_id]
			inner join sys.schemas as rs on rt.[schema_id] = rs.[schema_id]
		inner join sys.tables as ct on fk.parent_object_id = ct.[object_id]
		inner join sys.schemas as cs on ct.[schema_id] = cs.[schema_id]
		where
			rt.is_ms_shipped = 0 and ct.is_ms_shipped = 0
	)
-- create/drop foreign keys
select distinct
	'foreign keys' script_type
	, 
		' alter table ' + fki.constraintschemaname + '.' + fki.constrainttablename
		+ ' add constraint ' + fki.constraintname
		+ ' foreign key (' + fki.constraintcolumns + ')'
		+ ' references ' + fki.referenceschemaname + '.' + fki.referencetablename
		+ ' ('  + fki.referencecolumns + ');' create_script
	,
		'alter table ' + fki.constraintschemaname + '.' + fki.constrainttablename 
		+ 'drop constraint ' + fki.constraintname + ';' drop_script
from
	foreign_key_infos fki	
union all
-- create/drop unique constraints
select distinct
	'unique constraints'
	, 
		' alter table ' + uci.schemaname + '.' + uci.tablename
		+ ' add constraint ' + uci.constraintname
		+ ' unique ('
		+ stuff(
		(
			select ', ' + ci.columnname
			from unique_constraint_infos ci
			where ci.schemaname = uci.schemaname
				and ci.tablename = uci.tablename
				and ci.constraintname = uci.constraintname
			for xml path('')
		), 1, 1, '')
		+ ');'
	, 
		' alter table ' + uci.schemaname + '.' + uci.tablename
		+ ' drop constraint ' + uci.constraintname + ';'
from
	unique_constraint_infos uci
union all
-- create/drop check constraints
select distinct
	'check constraints'
	,
		'alter table ' + cki.schemaname + '.' + cki.tablename
		+ ' with check add constraint ' + cki.constraintname
		+ ' check ' + cki.definition + ';'
	,
		' alter table ' + cki.schemaname + '.' + cki.tablename
		+ ' drop constraint ' + cki.constraintname + ';'
from
	check_constraint_infos cki

Based on the work there: http://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

Heavily refactored because I can't take read mixed string building logic and involved system data retrieval, with CTE it makes it easier to look at each parts.

Brilliant.
Thank you!

you saved my live!