Find Missing Constraints

/*******************************************************************************************************************************************************************************************************************************
* Stored Procedure: LM_FindConstraintDiff 'Database1', 'Database2'
* Creation Date:  3/11/2002
* Copyright: Luke Malyurek
* Written by: Luke Malyurek
*
* Purpose: Stored Procedure to help compare two databases.  LM_FindConstraintDiff finds missing default, check,
*PrimaryKey, Unique, and ForeignKey constraints that are in one Database and missing from another.
*The Procedure also has the capability to script your missing constraints making getting your DB's
*in sync as easy as possible.  At this time, the procedure will script missing default, check, unique,
*and foreignkey constraints.  It is not generating PrimaryKey creation scripts.  The print option allows
*the user to see all of the constraints found within each database.
*
*
* Input Parameters: 
*@db1 Name of the first database, will be compared against @db2.
*@db2 Name of the second database.
*
* Local Variables: 
*@print int, Set to a 1 to see a listing of all constraints in each Database.
*@script int,Set to a 1 to have the procedure generate create scripts for missing constraints.
*
* Updates:
*
*******************************************************************************************************************************************************************************************************************************/
USE MASTER
GO

IF EXISTS(select * from sysobjects where name = 'LM_FindConstraintDiff' and type = 'p') 
BEGIN 
DROP PROCEDURE LM_FindConstraintDiff
END
go

CREATE PROCEDURE LM_FindConstraintDiff
@db1 varchar(255) = NULL,
@db2 varchar(255) = NULL

AS

set nocount on

declare
@cmd varchar(8000),
@fcolumn varchar(25),
@rcolumn varchar(25),
@keycount int,
@FKDB1 varchar(255),
@FKDB2 varchar(255),
@fkcount1 int,
@fkcount2 int,
@print int,
@script int,
@Table varchar(255),
@Table2 varchar(255),
@Column varchar(255),
@Column2 varchar(255),
@Value varchar(5000)


set @script = 1
set @print = 0
set @keycount = 0

/************************************************************************************************************
Error Checking
************************************************************************************************************/IF (@db1 = @db2)
BEGIN
print 'Database cannot be compared against itself.  End of work.'
return
END
/*
IF (@db1 is null or @db2 is null)
BEGIN
print 'Two valid database names must be provided.  End of work.'
return
END
*/IF NOT EXISTS(select * from master..sysdatabases where name = @db1)
BEGIN
print 'There is no database named: ' + @db1 + '.'
return
END
IF NOT EXISTS(select * from master..sysdatabases where name = @db2)
BEGIN
print 'There is no database named: ' + @db2 + '.'
return
END

/************************************************************************************************************
Default Constraints  ---------  DB1
************************************************************************************************************/create table ##DefaultFK1 ([Table] varchar(255), [Column] varchar(255), ConstraintName varchar(255), Composite varchar(5000), Value varchar(2000))
set @cmd = '
declare @DefaultBase table (constid int, [id] int, colid int)

insert @DefaultBase
select sysc.constid, sysc.id, sysc.colid
from '+@db1+'..sysobjects syso left join '+@db1+'..sysconstraints sysc on
syso.id = sysc.constid and syso.type = ''d''
where sysc.constid is not null 

insert ##DefaultFK1
select syso.name as [Table], syscol.name as [Column], syso1.name as [ConstraintName], syso.name + syscol.name + syscom.text, syscom.text
from @DefaultBase tmp left join '+@db1+'..sysobjects syso on tmp.id = syso.id 
left join '+@db1+'..syscolumns syscol on tmp.id = syscol.id and tmp.colid = syscol.colid
left join '+@db1+'..sysobjects syso1 on tmp.constid = syso1.id
left join '+@db1+'..syscomments syscom on tmp.constid = syscom.id
order by syso.name'

exec(@cmd)

/************************************************************************************************************
Default Constraints  ---------  DB2
************************************************************************************************************/create table ##DefaultFK2 ([Table] varchar(255), [Column] varchar(255), ConstraintName varchar(255), Composite varchar(5000), Value varchar(2000))
set @cmd = '
declare @DefaultBase table (constid int, [id] int, colid int)

insert @DefaultBase
select sysc.constid, sysc.id, sysc.colid
from '+@db2+'..sysobjects syso left join '+@db2+'..sysconstraints sysc on
syso.id = sysc.constid and syso.type = ''d''
where sysc.constid is not null 

Insert ##DefaultFK2
select syso.name as [Table], syscol.name as [Column], syso1.name as [ConstraintName], syso.name + syscol.name + syscom.text, syscom.text
from @DefaultBase tmp left join '+@db2+'..sysobjects syso on tmp.id = syso.id 
left join '+@db2+'..syscolumns syscol on tmp.id = syscol.id and tmp.colid = syscol.colid
left join '+@db2+'..sysobjects syso1 on tmp.constid = syso1.id
left join '+@db2+'..syscomments syscom on tmp.constid = syscom.id
order by syso.name'

exec(@cmd)

-- Find differences in Global temp tables.
print replicate('-', len(@db1) + len(@db2) + 48)
print 'Default Constraints missing from ' + upper(@db1)+' But found in '+upper(@db2)+'.'
print replicate('-', len(@db1) + len(@db2) + 48)

select d2.[Table], d2.[Column], d2.[ConstraintName], d2.[Value]
from ##Defaultfk2 d2 left join ##Defaultfk1 d1 on
d2.composite = d1.composite
where d1.composite is null

print replicate('-', len(@db1) + len(@db2) + 48)
print 'Default Constraints missing from ' +upper(@db2)+' But found in '+upper(@db1)+'.'
print replicate('-', len(@db1) + len(@db2) + 48)

select d1.[Table], d1.[Column], d1.[ConstraintName], d1.[Value]
from ##Defaultfk1 d1 left join ##Defaultfk2 d2 on
d1.composite = d2.composite
where d2.composite is null 

--Place IF condition here for printing purposes.
IF (@Print = 1)
BEGIN
  select * from ##DefaultFK1 
  select * from ##DefaultFK2
END


/************************************************************************************************************
Default Constraints  SCRIPTING SECTION
************************************************************************************************************/IF (@script = 1)
 BEGIN
 IF (Select count(*) from ##Defaultfk2 d2 
    left join ##Defaultfk1 d1 on d2.composite = d1.composite
    where d1.composite is null) > 0
  BEGIN
   print replicate('-', 228 + len(@db1))
   print 'Add default Constraints to '+@db1+':'+char(13)
   print replicate('-', 228 + len(@db1))

   declare DefaultStepIt1 cursor fast_forward read_only for
   select d2.[Table], d2.[Column], d2.[Value]
   from ##Defaultfk2 d2 left join ##Defaultfk1 d1 on
   d2.composite = d1.composite
   where d1.composite is null

   open DefaultStepIt1
   fetch next from DefaultStepIt1 into @Table, @Column, @Value

   WHILE (@@Fetch_Status <> -1)
    BEGIN
    set @cmd = 'ALTER TABLE '+@db1+'.[dbo].['+@Table+'] ADD CONSTRAINT [DF_'+@Table+'_'+@Column+'] 
    DEFAULT '+@Value+' FOR ['+@Column+']
    go'+char(13)
    print @cmd
    fetch next from DefaultStepIt1 into @Table, @Column, @Value
   END --WHILE
  close DefaultStepIt1
  Deallocate DefaultStepIt1
 END --If Count


 IF (select count(*)
 from ##Defaultfk1 d1 left join ##Defaultfk2 d2 on
 d1.composite = d2.composite
 where d2.composite is null) > 0
 BEGIN
   print replicate('-', 228 + len(@db2))
   print 'Add default Constraints to '+@db2+':'+char(13)
   print replicate('-', 228 + len(@db2))

   declare DefaultStepIt2 cursor fast_forward read_only for
   select d1.[Table], d1.[Column], d1.[Value]
   from ##Defaultfk1 d1 left join ##Defaultfk2 d2 on
   d1.composite = d2.composite
   where d2.composite is null

   open DefaultStepIt2
   fetch next from DefaultStepIt2 into @Table, @Column, @Value

   WHILE (@@Fetch_Status <> -1)
    BEGIN
    set @cmd = 'ALTER TABLE '+@db2+'.[dbo].['+@Table+'] ADD CONSTRAINT [DF_'+@Table+'_'+@Column+'] 
    DEFAULT '+@Value+' FOR ['+@Column+']
    go'+char(13)
    print @cmd
    fetch next from DefaultStepIt2 into @Table, @Column, @Value
   END --WHILE
  close DefaultStepIt2
  Deallocate DefaultStepIt2
 END --If Count
END --IF Script

/************************************************************************************************************
Default Table Cleanups
************************************************************************************************************/drop table ##defaultfk1
drop table ##defaultfk2


/************************************************************************************************************
Check Constraints --  DB1
************************************************************************************************************/create table ##CheckCon1 ([Table] varchar(255), [Column] varchar(255), [value] varchar(5000))

set @cmd = '
declare @CheckBase table (constid int, [id] int, colid int)

insert @CheckBase
select sysc.constid, sysc.id, sysc.colid
from '+@db1+'..sysobjects syso left join '+@db1+'..sysconstraints sysc on
syso.id = sysc.constid and syso.type = ''c''
where sysc.constid is not null

Insert ##CheckCon1
select syso.name as [Table], syscol.name as [Column], syscom.text as [value]
from @CheckBase tmp left join '+@db1+'..sysobjects syso on tmp.id = syso.id 
left join '+@db1+'..syscolumns syscol on tmp.id = syscol.id and tmp.colid = syscol.colid
left join '+@db1+'..syscomments syscom on tmp.constid = syscom.id 
order by syso.name'

exec(@cmd)

/************************************************************************************************************
Check Constraints --  DB2
************************************************************************************************************/create table ##CheckCon2 ([Table] varchar(255), [Column] varchar(255), [value] varchar(5000))

set @cmd = '
declare @CheckBase table (constid int, [id] int, colid int)

insert @CheckBase
select sysc.constid, sysc.id, sysc.colid
from '+@db2+'..sysobjects syso left join '+@db2+'..sysconstraints sysc on
syso.id = sysc.constid and syso.type = ''c''
where sysc.constid is not null

Insert ##CheckCon2
select syso.name as [Table], syscol.name as [Column], syscom.text as [value]
from @CheckBase tmp left join '+@db2+'..sysobjects syso on tmp.id = syso.id 
left join '+@db2+'..syscolumns syscol on tmp.id = syscol.id and tmp.colid = syscol.colid
left join '+@db2+'..syscomments syscom on tmp.constid = syscom.id 
order by syso.name'
exec(@cmd)

-- Find differences in Global temp tables.
print replicate('-', len(@db1) + len(@db2) + 48)
print 'Check Constraints missing from ' + upper(@db1)+' But found in '+upper(@db2)+'.'
print replicate('-', len(@db1) + len(@db2) + 48)

select d2.[Table], case when d2.[Column] IS NULL THEN 'Multiple Columns'  else d2.[Column] end as [Column], d2.[Value]
from ##CheckCon2 d2 left join ##CheckCon1 d1 on
d2.[Table] = d1.[Table] and d2.[Value] = d1.[Value]
where d1.[value] is null

print replicate('-', len(@db1) + len(@db2) + 48)
print 'Check Constraints missing from ' +upper(@db2)+' But found in '+upper(@db1)+'.'
print replicate('-', len(@db1) + len(@db2) + 48)

select d1.[Table], case when d1.[Column] IS NULL THEN 'Multiple Columns' else d1.[Column] end as [Column], d1.[Value]
from ##CheckCon1 d1 left join ##CheckCon2 d2 on
d1.[Table] = d2.[Table] and d1.[Value] = d2.[Value]
where d2.[Value] is null

IF (@Print = 1)
BEGIN
  select * from ##CheckCon1
  select * from ##CheckCon2
END

/************************************************************************************************************
Check Constraints  SCRIPTING SECTION
************************************************************************************************************/IF (@script = 1)
 BEGIN
 IF (Select count(*) from ##CheckCon1 d1 left join ##CheckCon2 d2 on
   d1.[Table] = d2.[Table] and d2.[Value] = d1.[Value]
   where d2.[Value] is null) > 0
  BEGIN
   print replicate('-', 228 + len(@db1))
   print 'Add Check Constraints to '+@db2+':'+char(13)
   print replicate('-', 228 + len(@db1))

   declare DefaultStepIt1 cursor fast_forward read_only for
   select d1.[Table], case when d1.[Column] IS NULL THEN 'MC' else d1.[Column] end as [Column], d1.[Value]
   from ##CheckCon1 d1 left join ##CheckCon2 d2 on
   d1.[Table] = d2.[Table] and d2.[Value] = d1.[Value]
   where d2.[Value] is null

   open DefaultStepIt1
   fetch next from DefaultStepIt1 into @Table, @Column, @Value

   WHILE (@@Fetch_Status <> -1)
    BEGIN
    set @cmd = 'ALTER TABLE '+@db2+'.[dbo].['+@Table+'] ADD CONSTRAINT [CK_'+@Table+'_'+@Column+'] 
    CHECK NOT FOR REPLICATION ' +@Value +'
    go'+char(13)
    print @cmd
    fetch next from DefaultStepIt1 into @Table, @Column, @Value
   END --WHILE
  close DefaultStepIt1
  Deallocate DefaultStepIt1
 END --If Count

 IF (select count(*)
   from ##CheckCon2 d2 left join ##CheckCon1 d1 on
   d2.[Table] = d1.[Table] and d2.[Value] = d1.[Value]
   where d1.[value] is null) > 0
 BEGIN
   print replicate('-', 228 + len(@db2))
   print 'Add Check Constraints to '+@db1+':'+char(13)
   print replicate('-', 228 + len(@db2))

   declare DefaultStepIt2 cursor fast_forward read_only for
   select d2.[Table], case when d2.[Column] IS NULL THEN 'MC'  else d2.[Column] end as [Column], d2.[Value]
   from ##CheckCon2 d2 left join ##CheckCon1 d1 on
   d2.[Table] = d1.[Table] and d2.[Value] = d1.[Value]
   where d1.[value] is null

   open DefaultStepIt2
   fetch next from DefaultStepIt2 into @Table, @Column, @Value

   WHILE (@@Fetch_Status <> -1)
    BEGIN
    set @cmd = 'ALTER TABLE '+@db1+'.[dbo].['+@Table+'] ADD CONSTRAINT [CK_'+@Table+'_'+@Column+'] 
    CHECK NOT FOR REPLICATION ' +@Value +'
    go'+char(13)
    print @cmd
    fetch next from DefaultStepIt2 into @Table, @Column, @Value
   END --WHILE
  close DefaultStepIt2
  Deallocate DefaultStepIt2
 END --If Count

END --IF Script

/************************************************************************************************************
Check Constraint Table Cleanups
************************************************************************************************************/drop table ##CheckCon1
drop table ##CheckCon2

/************************************************************************************************************
PK Constraints --  DB1
************************************************************************************************************/-- column definition for clustered should be a varchar with a case statement for population
create table ##PK1 ([Table] varchar(512), [Column] varchar(512), [Clustered] smallint, Composite varchar(512))

set @cmd = '
declare @PKBase table (constid int, [id] int)

insert @PKBase
select sysc.constid, sysc.id
from '+@db1+'..sysobjects syso left join '+@db1+'..sysconstraints sysc on
syso.id = sysc.constid and syso.type = ''k''
left join sysobjects syso1 on sysc.id = syso1.id
where sysc.constid is not null

Insert ##PK1
select syso.name, syscol.name, sysi.indid, syso.name +''-''+ syscol.name
from @PKBase tmp 
left join '+@db1+'..sysobjects syso on tmp.id = syso.id --Got table object ex. table name / need constraint name now
left join '+@db1+'..sysobjects syso1 on tmp.constid = syso1.id
left join '+@db1+'..sysindexes sysi on syso.id = sysi.id and syso1.name = sysi.name -- now I Have the PK Index record
left join '+@db1+'..sysindexkeys sysikey on sysi.id = sysikey.id and sysi.indid = sysikey.indid --now have the indexkey record
left join '+@db1+'..syscolumns syscol on syso.id = syscol.id and sysikey.colid = syscol.colid
order by syso.name'

exec(@cmd)

/************************************************************************************************************
PK Constraints --  DB2
************************************************************************************************************/-- column definition for clustered should be a varchar with a case statement for population
create table ##PK2 ([Table] varchar(255), [Column] varchar(255), [Clustered] smallint, Composite varchar(255))

set @cmd = '
declare @PKBase table (constid int, [id] int)

insert @PKBase
select sysc.constid, sysc.id
from '+@db2+'..sysobjects syso left join '+@db2+'..sysconstraints sysc on
syso.id = sysc.constid and syso.type = ''k''
left join sysobjects syso1 on sysc.id = syso1.id
where sysc.constid is not null

Insert ##PK2
select syso.name, syscol.name, sysi.indid, syso.name +''-''+ syscol.name
from @PKBase tmp 
left join '+@db2+'..sysobjects syso on tmp.id = syso.id --Got table object ex. table name / need constraint name now
left join '+@db2+'..sysobjects syso1 on tmp.constid = syso1.id
left join '+@db2+'..sysindexes sysi on syso.id = sysi.id and syso1.name = sysi.name -- now I Have the PK Index record
left join '+@db2+'..sysindexkeys sysikey on sysi.id = sysikey.id and sysi.indid = sysikey.indid --now have the indexkey record
left join '+@db2+'..syscolumns syscol on syso.id = syscol.id and sysikey.colid = syscol.colid
order by syso.name'

exec(@cmd)

-- Find differences in Global temp tables.
print replicate('-', len(@db1) + len(@db2) + 48)
print 'PK Constraints missing from ' + upper(@db1)+' But found in '+upper(@db2)+'.'
print replicate('-', len(@db1) + len(@db2) + 48)

select d2.[Table], d2.[Column],
case d2.[Clustered]
  when 1 then 'CLUSTERED'
  else 'NON-CLUSTERED'
end as [Clustered]
from ##PK2 d2
where d2.composite not in (select composite from ##pk1)

print replicate('-', len(@db1) + len(@db2) + 48)
print 'PK Constraints missing from ' +upper(@db2)+' But found in '+upper(@db1)+'.'
print replicate('-', len(@db1) + len(@db2) + 48)

select d1.[Table], d1.[Column], 
case d1.[Clustered]
  when 1 then 'CLUSTERED'
  else 'NON-CLUSTERED'
end as [Clustered]
from ##PK1 d1  
where d1.composite not in (select composite from ##pk2)

IF (@Print = 1)
BEGIN
  select [Table], [Column], 
  CASE  [Clustered]
    when 1 then 'CLUSTERED'
    else 'NON-CLUSTERED'
  end as [Clustered]
  from ##PK1 order by [table],[column] 

  select [Table], [Column], 
  CASE  [Clustered]
    when 1 then 'CLUSTERED'
    else 'NON-CLUSTERED'
  end as [Clustered]
  from ##PK2 order by [table],[column]
END


/************************************************************************************************************
PK Constraint Table Cleanups
************************************************************************************************************/
drop table ##PK1
drop table ##PK2

/************************************************************************************************************
FK Constraint Section
************************************************************************************************************/
create table #keycount (keycount int)

set @cmd = 'insert #keycount select max(keycnt) from '+@db1+'..sysreferences'
exec(@cmd)
set @keycount = (select * from #keycount)
drop table #keycount

set @cmd = 'create table ##'+@db1+' (constid int, fkeyid int, rkeyid int, keycnt int, fkeycol int, rkeycol int)'
exec(@cmd)

while (@keycount > 0)
begin
set @fcolumn = 'fkey' + cast(@keycount as varchar(25))
set @rcolumn = 'rkey' + cast(@keycount as varchar(25))

set @cmd = 'insert ##'+@db1+' (constid, fkeyid, rkeyid, keycnt, fkeycol, rkeycol)
select constid, fkeyid, rkeyid, keycnt, '+@fcolumn+','+@rcolumn+'
from '+@db1+'..sysreferences
where keycnt = '+ cast(@keycount as varchar(25))
exec(@cmd)
set @keycount = @keycount - 1
end

--Populate the table containing FK's for DB1 --> @FKDB1
set @FKDB1 = '##FK'+@db1
set @cmd = 'select syso.name AS Table1, sysc.name AS [Table 1 Column], 
syso2.name AS [Constraint], 
syso1.name AS Table2, sysc2.name as [Table 2 Column] into '+@FKDB1+'
from ##'+@db1+' sysme left join '+@db1+'..syscolumns sysc on
sysme.fkeyid = sysc.id and sysme.fkeycol = sysc.colid 
join '+@db1+'..syscolumns sysc2 on
sysme.rkeyid = sysc2.id and sysme.rkeycol = sysc2.colid
left join '+@db1+'..sysobjects syso on sysme.fkeyid = syso.id
left join '+@db1+'..sysobjects syso1 on sysme.rkeyid = syso1.id
Left join '+@db1+'..sysobjects syso2 on sysme.constid = syso2.id
order by 1,2,4,5'

exec(@cmd)

---------------------------------------------------------------------------------------------------------------------
set @keycount = 0

create table #keycount1 (keycount int)
set @cmd = 'insert #keycount1 select max(keycnt) from '+@db2+'..sysreferences'
exec(@cmd)
set @keycount = (select * from #keycount1)
drop table #keycount1

set @cmd = 'create table ##'+@db2+' (constid int, fkeyid int, rkeyid int, keycnt int, fkeycol int, rkeycol int)'
exec(@cmd)

while (@keycount > 0)
begin
set @fcolumn = 'fkey' + cast(@keycount as varchar(25))
set @rcolumn = 'rkey' + cast(@keycount as varchar(25))

set @cmd = 'insert ##'+@db2+' (constid, fkeyid, rkeyid, keycnt, fkeycol, rkeycol)
select constid, fkeyid, rkeyid, keycnt, '+@fcolumn+','+@rcolumn+'
from '+@db2+'..sysreferences
where keycnt = '+ cast(@keycount as varchar(25))
exec(@cmd)
set @keycount = @keycount - 1
end

--Populate the table containing FK's for DB2 --> @FKDB2
set @FKDB2 = '##FK'+@db2
set @cmd = 'select syso.name AS Table1, sysc.name AS [Table 1 Column], 
syso2.name AS [Constraint], 
syso1.name AS Table2, sysc2.name as [Table 2 Column] into '+@FKDB2+'
from ##'+@db2+' sysme left join '+@db2+'..syscolumns sysc on
sysme.fkeyid = sysc.id and sysme.fkeycol = sysc.colid 
join '+@db2+'..syscolumns sysc2 on
sysme.rkeyid = sysc2.id and sysme.rkeycol = sysc2.colid
left join '+@db2+'..sysobjects syso on sysme.fkeyid = syso.id
left join '+@db2+'..sysobjects syso1 on sysme.rkeyid = syso1.id
Left join '+@db2+'..sysobjects syso2 on sysme.constid = syso2.id
order by 1,2,4,5'

exec(@cmd)


--Start Comparing Data

set @cmd = 'select count(*) as row into ##counter1 from '+@FKDB1
exec(@cmd) 
set @cmd = 'select count(*) as row into ##counter2 from '+@FKDB2
exec(@cmd)
set @fkcount1 = (select row from ##counter1)
set @fkcount2 = (select row from ##counter2)
drop table ##counter1
drop table ##counter2


print replicate('-',59)
print 'FK''s found in '+@db1+ ' but not in '+@db2+ '.'
print replicate('-',59)
set @cmd = '
  select one.Table1, one.[Table 1 Column], one.[Constraint], one.Table2, one.[Table 2 Column]
  into ##FKMiss1
  from '+@FKDB1+ ' one left join '+@FKDB2+ ' two on
  one.Table1 = Two.Table1 and one.[Table 1 Column] = two.[Table 1 Column] and
  one.Table2 = Two.Table2 and one.[Table 2 Column] = two.[Table 2 Column]
  Where two.table1 is null'
exec(@cmd)
set @cmd = 'select * from ##FKMiss1'
exec(@cmd)
print replicate('-',59)

print replicate('-',59)
print 'FK''s found in '+@db2+ ' but not in '+@db1+ '.'
print replicate('-',59)
set @cmd = '
  select one.Table1, one.[Table 1 Column], one.[Constraint], one.Table2, one.[Table 2 Column]
  into ##FKMiss2
  from '+@FKDB2+ ' one left join '+@FKDB1+ ' two on
  one.Table1 = Two.Table1 and one.[Table 1 Column] = two.[Table 1 Column] and
  one.Table2 = Two.Table2 and one.[Table 2 Column] = two.[Table 2 Column]
  Where two.table1 is null'
exec(@cmd)
set @cmd = 'Select * from ##FKMiss2'
exec(@cmd)
print replicate('-',59)

IF (@Print = 1)
BEGIN
  Print 'ForeignKey Constraints found in ' + @DB1 +'.'
  set @cmd = 'SELECT * FROM '+@FKDB1+' ORDER BY Table1, [Table 1 Column]'
  EXEC (@cmd)
  Print 'ForeignKey Constraints found in ' + @DB2 +'.'
  set @cmd = 'SELECT * FROM '+@FKDB2+' ORDER BY Table1, [Table 1 Column]'
  EXEC (@cmd)
END

/************************************************************************************************************
Foreignkeys Constraint  SCRIPTING SECTION
************************************************************************************************************/IF (@script = 1)
 BEGIN
 IF (select count(*) from ##FKMiss1) > 0
  BEGIN
   print replicate('-', 228 + len(@db1))
   print 'Add Foreignkey Constraints to '+@db2+':'+char(13)
   print replicate('-', 228 + len(@db1))

   declare DefaultStepIt1 cursor fast_forward read_only for
   select Table1, [Table 1 Column], Table2, [Table 2 Column] from ##FKMiss1

   open DefaultStepIt1
   fetch next from DefaultStepIt1 into @Table, @Column, @Table2, @Column2

   WHILE (@@Fetch_Status <> -1)
    BEGIN
    set @cmd = 'ALTER TABLE '+@db2+'.[dbo].['+@Table+'] ADD CONSTRAINT [FK_'+@Table+'_'+@Column+'] 
    FOREIGN KEY ( ['+@Column+']) REFERENCES ['+@Table2+'] (['+@Column2+'])
    go'+char(13)
    print @cmd
    fetch next from DefaultStepIt1 into @Table, @Column, @Table2, @Column2
   END --WHILE
  close DefaultStepIt1
  Deallocate DefaultStepIt1
 END --If Count

 IF (select count(*) from ##FKMiss2) > 0
 BEGIN
   print replicate('-', 228 + len(@db2))
   print 'Add Foreignkey Constraints to '+@db1+':'+char(13)
   print replicate('-', 228 + len(@db2))

   declare DefaultStepIt2 cursor fast_forward read_only for
   select Table1, [Table 1 Column], Table2, [Table 2 Column] from ##FKMiss2

   open DefaultStepIt2
   fetch next from DefaultStepIt2 into @Table, @Column, @Table2, @Column2

   WHILE (@@Fetch_Status <> -1)
    BEGIN
    set @cmd = 'ALTER TABLE '+@db1+'.[dbo].['+@Table+'] ADD CONSTRAINT [FK_'+@Table+'_'+@Column+'] 
    FOREIGN KEY ( ['+@Column+']) REFERENCES ['+@Table2+'] (['+@Column2+'])
    go'+char(13)
    print @cmd
    fetch next from DefaultStepIt2 into @Table, @Column, @Table2, @Column2
   END --WHILE
  close DefaultStepIt2
  Deallocate DefaultStepIt2
 END --If Count
END --IF Script 



/************************************************************************************************************
FK Constraint Table Cleanups
************************************************************************************************************/
set @cmd = 'drop table ##'+@db1
exec(@cmd)

set @cmd = 'drop table ##'+@db2
exec(@cmd)

set @cmd = 'drop table '+@FKDB1
exec(@cmd)

set @cmd = 'drop table '+@FKDB2
exec(@cmd)

drop table ##FKMiss1
drop table ##FKMiss2