Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys.  But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,

--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable1 primary key (id1, id2, id3))

create table dbo.fktable2(
  id int not null identity(1,1) primary key
, id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY (id1, id2, id3) 
  REFERENCES dbo.fktable1 (id1, id2, id3))

Combining those multiple records in the sys.foreign_key_columns into a concatenated string in order to get this is tricky:

--Script 2
ALTER TABLE [dbo].[fktable2]  WITH CHECK 
  ADD  CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY([id1], [id2], [id3])
  REFERENCES [dbo].[fktable1] ([id1], [id2], [id3])

Here's how I recently did this.   It actually turned out to be more complicated than I thought, certainly more complicated that your standard throw-strings-together-based-on-system-tables.  This is because we need to build a recurse of the multi-key values that are both referenced and referencing in foreign keys. 

--Script 3

WITH cteColumnNames_Base (FKName, ReferencingColumnNames, FKingRank,  ReferencedColumnNames, FKedRank)
 as ( SELECT FKName     = f.name 
  ,  ReferencingColumnNames = CAST(c.name as varchar(8000))
  ,  FKingRank    = ROW_NUMBER() OVER (PARTITION BY f.Name ORDER BY c.column_id )
  ,  ReferencedColumnNames = CAST(rc.name as varchar(8000))
  ,  FKedRank    = ROW_NUMBER() OVER (PARTITION BY f.Name ORDER BY rc.column_id)
  FROM  sys.foreign_keys f 
  inner join sys.objects o on f.parent_object_Id = o.object_id 
  inner join sys.schemas s on o.schema_id = s.schema_id
  inner join sys.objects ro on f.referenced_object_Id = ro.object_id 
  inner join sys.schemas rs on ro.schema_id = rs.schema_id
  inner join sys.foreign_key_columns fc on fc.constraint_object_id = f.object_id and fc.parent_object_id = o.object_id and fc.referenced_object_id = ro.object_id
  inner join sys.columns c on c.object_id = o.object_id and c.column_id = fc.parent_column_id
  inner join sys.columns rc on ro.object_id = rc.object_id and rc.column_id = fc.referenced_column_id
 )
, cteColumnNames_Concat (FKName, ReferencingColumnNames, ReferencedColumnNames, FKingRank, FKedRank )
as (SELECT FKName
  , ReferencingColumnNames
  , ReferencedColumnNames
  , FKingRank 
  , FKedRank
 FROM cteColumnNames_Base
 where FKingRank = 1
 or FKedRank = 1
 UNION ALL
 SELECT  b.FKName
  , c.ReferencingColumnNames + '], [' + b.ReferencingColumnNames
  , c.ReferencedColumnNames + '], [' + b.ReferencedColumnNames
  , b.FKingRank
  , b.FKedRank
 FROM cteColumnNames_Base b
 INNER JOIN cteColumnNames_Concat c
 on b.FKName = c.FKName
 and (b.FKingRank = c.FKingRank + 1
 or b.FKedRank = c.FKedRank + 1)
 )
, cteReferencingColumnNames (FKName, ReferencingColumnNames, TopRank)
as (SELECT FKName
  , ReferencingColumnNames
  , TopRank     = RANK() OVER (PARTITION BY FKName ORDER BY FKingRank Desc)
 FROM  cteColumnNames_Concat c )
, cteReferencedColumnNames (FKName, ReferencedColumnNames, TopRank)
as (SELECT FKName
  , ReferencedColumnNames
  , TopRank     = RANK() OVER (PARTITION BY FKName ORDER BY FKedRank Desc)
 FROM  cteColumnNames_Concat c ) 
select distinct
 FKName = f.name 
, ReferencingTableName = s.name + '.' + o.name
, ReferencingColumnName = '[' + con.ReferencingColumnNames + ']' 
, ReferencedTableName = rs.name + '.' + ro.name
, ReferencedColumnName = '[' + rcon.ReferencedColumnNames + ']' 
, [TSQL] ='ALTER TABLE [' + s.name + '].[' + o.name + ']  WITH CHECK ADD  CONSTRAINT [' + f.name + '] FOREIGN KEY([' + con.ReferencingColumnNames + ']) 
REFERENCES [' + rs.name + '].[' + ro.name + '] (['+rcon.ReferencedColumnNames+']) '
+ 'ON UPDATE  ' + CASE update_referential_action WHEN 0 THEN 'No action'
             WHEN 1 THEN 'Cascade'
             WHEN 2 THEN 'Set null'
             WHEN 3 THEN 'Set default' END 
+ ' ON DELETE ' + CASE delete_referential_action WHEN 0 THEN 'No action'
             WHEN 1 THEN 'Cascade'
             WHEN 2 THEN 'Set null'
             WHEN 3 THEN 'Set default' END 

--select *
FROM  sys.foreign_keys f 
inner join sys.objects o on f.parent_object_Id = o.object_id 
inner join sys.schemas s on o.schema_id = s.schema_id
inner join sys.objects ro on f.referenced_object_Id = ro.object_id 
inner join sys.schemas rs on ro.schema_id = rs.schema_id
inner join sys.foreign_key_columns fc on fc.constraint_object_id = f.object_id and fc.parent_object_id = o.object_id and fc.referenced_object_id = ro.object_id
inner join sys.columns c on c.object_id = o.object_id and c.column_id = fc.parent_column_id
inner join sys.columns rc on ro.object_id = rc.object_id and rc.column_id = fc.referenced_column_id
inner join cteReferencingColumnNames con on con.FKName = f.Name and con.TopRank = 1
inner join cteReferencedColumnNames rcon on rcon.FKName = f.Name and rcon.TopRank = 1
order by f.name

And here's the results:

The right-most column (cut off for size purposes) produces the necessary TSQL to recreate the foreign key if necessary.  Compare to Script 2 above.

--Script 4
ALTER TABLE [dbo].[fktable2]  WITH CHECK  
ADD  CONSTRAINT [FK_fktable1_fktable2] 
FOREIGN KEY([id1], [id2], [id3])
REFERENCES [dbo].[fktable1] ([id1], [id2], [id3]);

Below is the complete test script set up to create several testing tables with various foreign key relationships: 

--Script 5
create table dbo.fktable1
( id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable1 primary key (id1, id2, id3)
)
create table dbo.fktable2
( id int not null identity(1,1) primary key
,      id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable1_fktable2] FOREIGN KEY (id1, id2, id3) REFERENCES dbo.fktable1 (id1, id2, id3)
)
create table dbo.fktable3
( id1 int not null
, id2 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable3 primary key (id1, id2)
)
create table dbo.fktable4
( id int not null identity(1,1) primary key
,      id1 int not null
, id2 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable3_fktable4] FOREIGN KEY (id1, id2) REFERENCES dbo.fktable3 (id1, id2)
)
create table dbo.fktable5
( id1 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable5 primary key (id1)
)
create table dbo.fktable6
( id int not null identity(1,1) primary key
,      id1 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable5_fktable6] FOREIGN KEY (id1) REFERENCES dbo.fktable5 (id1)
)
create table dbo.fktable7
( id1 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable7 primary key (id1)
)
create table dbo.fktable8
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id2 int not null
, CONSTRAINT [FK_fktable7_fktable8] FOREIGN KEY (id2) REFERENCES dbo.fktable7 (id1)
)
create table dbo.fktable9
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id3 int not null
, CONSTRAINT [FK_fktable7_fktable9] FOREIGN KEY (id3) REFERENCES dbo.fktable7 (id1)
)
create table dbo.fktable10
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id4 int not null
,      id5 int not null
, CONSTRAINT [FK_fktable9_fktable10] FOREIGN KEY (id4) REFERENCES dbo.fktable9 (id)
, CONSTRAINT [FK_fktable7_fktable10] FOREIGN KEY (id5) REFERENCES dbo.fktable7 (id1)
)
create table dbo.fktable11
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id10 int not null
, CONSTRAINT [FK_fktable11_fktable10] FOREIGN KEY (id10) REFERENCES dbo.fktable10 (id)
)

--Test values
insert into dbo.fktable1 (id1, id2, id3, text1) values (1, 2, 3, 'test1'), (4, 5, 6, 'test2'), (7, 8, 9, 'test3')
insert into dbo.fktable2 (id1, id2, id3, text1) values (1, 2, 3, 'test1')
insert into dbo.fktable2 (id1, id2, id3, text1) values (4, 5, 6, 'test2')
insert into dbo.fktable3 (id1, id2, text1) values (1, 2, 'test1'), (4, 5, 'test2'), (7, 8, 'test3')
insert into dbo.fktable4 (id1, id2,  text1) values (1, 2,  'test1')
insert into dbo.fktable4 (id1, id2,  text1) values (4, 5,  'test2')
insert into dbo.fktable5 (id1,  text1) values (1,  'test1'), (4,  'test2'), (7,  'test3')
insert into dbo.fktable6 (id1, text1) values (1, 'test1')
insert into dbo.fktable6 (id1, text1) values (4, 'test2')
insert into dbo.fktable7 (id1,  text1) values (1,  'test1'), (4,  'test2'), (7,  'test3')
insert into dbo.fktable8 (id2, text1) values (1, 'test1')
insert into dbo.fktable8 (id2, text1) values (4, 'test2')
insert into dbo.fktable9 (id3, text1) values (7, 'test3')
insert into dbo.fktable9 (id3, text1) values (4, 'test2')
insert into dbo.fktable10 (id4, id5, text1) values (1, 4, 'test4')
insert into dbo.fktable10 (id4, id5, text1) values (2, 7, 'test7')
insert into dbo.fktable11 (id10,  text1) values (1,  'test10')
insert into dbo.fktable11 (id10,  text1) values (2,  'test11')

Edit: Aug 30 2013: Added the UPDATE and DELETE actions to the script.