CREATE
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
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
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
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.