Foreign Key Generation from Common Column Names - SQL Server

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

The following SQL will generate a set of SQL statements to create foreign keys on all tables in your database, based on a column having the same name as the primary key field in another table.

{copytext|sql}
with pk as (select * from dbo.DbLayout where IsPkey = 1)
,other as (select * from dbo.DbLayout where IsPkey = 0)
select 
     pk.ColumnName
    ,PkTable        = pk.TableName
    ,OnTable        = o.TableName
    ,sql            = 'alter table [' + o.TableName + '] with check add constraint [FK_' 
                        + o.TableName + '_' + pk.TableName + '] foreign key ([' + pk.ColumnName 
                        + ']) references [' + pk.TableName + '] ([' + pk.ColumnName + '])'
from 
    pk
    inner join other o
        on pk.ColumnName = o.ColumnName
order by
    1,2