Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SqlServer: Generate alter add / drop for all type of constraints (foreign key, unique and check)
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
@smoothdeveloper

This comment has been minimized.

Copy link
Owner Author

commented Feb 27, 2015

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.

@itsho

This comment has been minimized.

Copy link

commented Nov 17, 2015

Brilliant.
Thank you!

@travelerspb

This comment has been minimized.

Copy link

commented Dec 3, 2015

you saved my live!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.