Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Foreign Key Generation from Common Column Names - SQL Server

RSS
Modified on Tue, Mar 08, 2011, 2:28 PM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.