Skip to content

Instantly share code, notes, and snippets.

@smoothdeveloper

smoothdeveloper/generate_add_and_drop_all_constraints.sql

Created February 27, 2015 13:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save smoothdeveloper/ea48e43aead426248c0f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save smoothdeveloper/ea48e43aead426248c0f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. smoothdeveloper created this gist Feb 27, 2015.
    121 changes: 121 additions & 0 deletions generate_add_and_drop_all_constraints.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,121 @@
    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