Automatically add Foreign Keys

 
declare @code nvarchar(4000)
declare @template nvarchar(4000)
declare @PRIM_TABLE_SCHEMA sysname
declare @PRIM_TABLE_NAME sysname
declare @PRIM_COLUMN_NAME sysname
declare @CHILD_TABLE_SCHEMA sysname
declare @CHILD_TABLE_NAME sysname
declare @CHILD_COLUMN_NAME sysname
declare @rowid bigint
declare @maxrows bigint
declare @guid uniqueidentifier
 
set @template = 
'ALTER TABLE <<CHILD_TABLE_SCHEMA>>.<<CHILD_TABLE_NAME>> ADD CONSTRAINT
 FK_<<CHILD_TABLE_NAME>>_<<PRIM_TABLE_NAME>>_<<GUID>> FOREIGN KEY
 (
 <<CHILD_COLUMN_NAME>>
 ) REFERENCES <<PRIM_TABLE_SCHEMA>>.<<PRIM_TABLE_NAME>>
 (
 <<PRIM_COLUMN_NAME>>
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#primkeys') is not null
 drop table #primkeys
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #primkeys
from 
 INFORMATION_SCHEMA.TABLES t
 JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS con
  on con.TABLE_SCHEMA = t.TABLE_SCHEMA 
   and con.TABLE_NAME = t.TABLE_NAME 
            AND con.CONSTRAINT_TYPE = 'PRIMARY KEY'
 join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
  on con.CONSTRAINT_SCHEMA = u.CONSTRAINT_SCHEMA 
   and con.CONSTRAINT_NAME = u.CONSTRAINT_NAME 
 join INFORMATION_SCHEMA.COLUMNS col
  on u.TABLE_SCHEMA = col.TABLE_SCHEMA
   and u.TABLE_NAME = col.TABLE_NAME
   and u.COLUMN_NAME = col.COLUMN_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#NonKeyColumns') is not null
 drop table #NonKeyColumns
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #NonKeyColumns
from 
 INFORMATION_SCHEMA.TABLES t
 join INFORMATION_SCHEMA.COLUMNS col
  on t.TABLE_SCHEMA = col.TABLE_SCHEMA
   and t.TABLE_NAME = col.TABLE_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
except
 
select
 *
from
 #primkeys
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#colMappings') is not null
 drop table #colMappings
 
 
select
 ROW_NUMBER() over(order by p.TABLE_SCHEMA, p.TABLE_NAME, p.COLUMN_NAME,
  n.TABLE_SCHEMA, n.TABLE_NAME, n.COLUMN_NAME) as  RowID
 ,p.TABLE_SCHEMA as PRIM_TABLE_SCHEMA
 ,p.TABLE_NAME as PRIM_TABLE_NAME
 ,p.COLUMN_NAME as PRIM_COLUMN_NAME
 ,n.TABLE_SCHEMA as CHILD_TABLE_SCHEMA
 ,n.TABLE_NAME as CHILD_TABLE_NAME
 ,n.COLUMN_NAME as CHILD_COLUMN_NAME
into #colMappings
from
 #primkeys p
 join #NonKeyColumns n
  on (
   p.TABLE_SCHEMA != n.TABLE_SCHEMA
   or p.TABLE_NAME != n.TABLE_NAME
   )
   and p.COLUMN_NAME = n.COLUMN_NAME
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
select 
 @rowid = 1
 ,@maxrows = MAX(RowID)
from
 #colMappings
 
 
while (@rowid <= @maxrows)
begin
 
 select
  @PRIM_TABLE_SCHEMA = PRIM_TABLE_SCHEMA
  ,@PRIM_TABLE_NAME = PRIM_TABLE_NAME
  ,@PRIM_COLUMN_NAME = PRIM_COLUMN_NAME
  ,@CHILD_TABLE_SCHEMA = CHILD_TABLE_SCHEMA
  ,@CHILD_TABLE_NAME = CHILD_TABLE_NAME
  ,@CHILD_COLUMN_NAME = CHILD_COLUMN_NAME
  ,@guid = NEWID()
 from
  #colMappings m
 where
  m.RowID = @rowid
 
 
 set @code = replace(@template, '<<PRIM_TABLE_SCHEMA>>', @PRIM_TABLE_SCHEMA)
 set @code = replace(@code, '<<PRIM_TABLE_NAME>>', @PRIM_TABLE_NAME)
 set @code = replace(@code, '<<PRIM_COLUMN_NAME>>', @PRIM_COLUMN_NAME)
 set @code = replace(@code, '<<CHILD_TABLE_SCHEMA>>', @CHILD_TABLE_SCHEMA)
 set @code = replace(@code, '<<CHILD_TABLE_NAME>>', @CHILD_TABLE_NAME)
 set @code = replace(@code, '<<CHILD_COLUMN_NAME>>', @CHILD_COLUMN_NAME)
 set @code = replace(@code, '<<GUID>>', replace(@guid, '-', ''))
 
 
 print @code
 
 --exec sp_executesql @code
 
 set @rowid = @rowid +1
end