Copy Every Foreign Key - SQL Server

This page is part of the Foreign Key Pages collection.
Click the icon to see the index.

Solution #1

This solution will not include any foreign keys in your database that reference multiple fields. The SQL in Step 1 lists these foreign keys, which you will need to script manually. The SQL in Step 2 scripts the CREATE scripts for all other foreign keys.

Step 1 - Check for Multi-Column Foreign Keys

select distinct
    foreignKey = foreignKey.name 

from 
    sysforeignkeys fk

    inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid

    inner join sysobjects onTable 
        on fk.fkeyid = onTable.id

    inner join syscolumns onColumn
        on fk.fkeyid = onColumn.id
        and fk.fkey = onColumn.colid

    inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id

    inner join syscolumns againstColumn
        on fk.rkeyid = againstColumn.id
        and fk.rkey = againstColumn.colid

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'

group by 
    foreignKey.name

having 
    count(1) > 1

order by 
    1

Step #2 - Script All Single-Column Foreign Keys

select distinct
    sql = 'alter table [' + onTable.name + '] with '
            + case 
                when ObjectProperty(fk.constid,'CnstIsDisabled') <> 0 
                then 'NOCHECK'
                else 'check'
                end
            + ' add constraint [' 
            + foreignKey.name + '] foreign key([' + onColumn.name + ']) references [' 
            + againstTable.name + '] ([' + againstColumn.name + '])'
from 
    sysforeignkeys fk

    inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid

    inner join sysobjects onTable 
        on fk.fkeyid = onTable.id

    inner join syscolumns onColumn
        on fk.fkeyid = onColumn.id
        and fk.fkey = onColumn.colid

    inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id

    inner join syscolumns againstColumn
        on fk.rkeyid = againstColumn.id
        and fk.rkey = againstColumn.colid

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'
	and foreignKey.Name not in (
        select distinct
            foreignKey.name 
        
        from 
            sysforeignkeys fk
        
            inner join sysobjects foreignKey 
                on foreignKey.id = fk.constid
        
            inner join sysobjects onTable 
                on fk.fkeyid = onTable.id
        
            inner join syscolumns onColumn
                on fk.fkeyid = onColumn.id
                and fk.fkey = onColumn.colid
        
            inner join sysobjects againstTable 
                on fk.rkeyid = againstTable.id
        
            inner join syscolumns againstColumn
                on fk.rkeyid = againstColumn.id
                and fk.rkey = againstColumn.colid
        
        where 1=1
            AND againstTable.TYPE = 'U'
            AND onTable.TYPE = 'U'
        
        group by 
            foreignKey.name
        
        having 
            count(1) > 1
        )

order by
    1

Solution #2

This solution handles all foreign keys in the current database, including multi-column foreign keys. Unfortunately, it is untested as of this writing, so it may contain small errors.

with MyColumns as (
    select distinct
         OnTable        = onTable.Name
        ,ForeignKey     = foreignKey.Name
        ,OnColumn       = onColumn.Name
        ,AgainstTable   = againstTable.name
        ,AgainstColumn  = againstColumn.name
        ,ColOrder       = fk.fkey
        ,Enforce        = case 
                             when ObjectProperty(fk.constid,'CnstIsDisabled') <> 0 
                                 then 'nocheck'
                             else 'check'
                             end
    from 

        sysforeignkeys fk

        inner join sysobjects foreignKey 
            on foreignKey.id = fk.constid

        inner join sysobjects onTable 
            on fk.fkeyid = onTable.id

        inner join syscolumns onColumn
            on fk.fkeyid = onColumn.id
            and fk.fkey = onColumn.colid

        inner join sysobjects againstTable 
            on fk.rkeyid = againstTable.id

        inner join syscolumns againstColumn
            on fk.rkeyid = againstColumn.id
            and fk.rkey = againstColumn.colid

    where 1=1
        AND againstTable.TYPE = 'U'
        AND onTable.TYPE = 'U'
    )
,FkColumns as (
    select
         OnTable
        ,ForeignKey
        ,OnColumn
        ,AgainstTable
        ,AgainstColumn
        ,ColOrder
        ,ColMax = max(ColOrder) over (partition by OnTable, AgainstTable, ForeignKey)
        ,Enforce
    from 
        MyColumns
    )
,FkTables as (
    select distinct
         OnTable
        ,AgainstTable
        ,ForeignKey
        ,Enforce
    from
        FkColumns
    )

select distinct
     OnTable
    ,ForeignKey
    ,SortOrder      = 10
    ,ColOrder       = 0
    ,sql            = 'alter table [' + onTable + '] with ' + Enforce 
                        + ' add constraint [' + ForeignKey + '] foreign key ('
from 
    FkTables

union select
     OnTable
    ,ForeignKey
    ,SortOrder      = 20
    ,ColOrder
    ,sql            = '    [' + OnColumn + ']' + case when ColOrder = ColMax then ' ' else ', ' end
from
    FkColumns

union select
     OnTable
    ,ForeignKey
    ,SortOrder      = 30
    ,ColOrder       = 0
    ,sql            = '    )'
from
    FkTables

union select
     OnTable
    ,ForeignKey
    ,SortOrder      = 35
    ,ColOrder       = 0
    ,sql            = '    references [' + AgainstTable + '] ('
from
    FkTables

union select
     OnTable
    ,ForeignKey
    ,SortOrder      = 40
    ,ColOrder
    ,sql            = '    [' + AgainstColumn + ']' + case when ColOrder = ColMax then ' ' else ', ' end
from
    FkColumns

union select
     OnTable
    ,ForeignKey
    ,SortOrder      = 50
    ,ColOrder       = 0
    ,sql            = '    )'
from
    FkTables

order by 
     OnTable
    ,ForeignKey
    ,SortOrder
    ,ColOrder