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+']);' 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')
