/**********************************************************************

Procedure Name:  [Add_Reference]

Created By:      Mangesh Singh

Date:            24 April 2009

Purpose:         To Add Relationship In Database

**********************************************************************/

ALTER Procedure [dbo].[Add_Reference] 

AS

DECLARE @ERR INT 

Begin Tran Reference

Create Table #temp 

(ID INT IDENTITY,TableName varchar(100),IDD INT) 

Insert Into #temp(TableName,IDD) 

Select Name,ID From sys.sysobjects Where xtype='U' 

Declare @Count INT 

declare @A INT 

declare @PrimaryColumn varchar(100) 

declare @primarytable varchar(100) 

declare @ForegnKeyTable varchar(100) 

declare @Sql varchar(max) 

Set @A=1 

Select @Count=Count(*) From #temp 

While(@A<=@Count) 

Begin 

  Declare @IsPrimary Bit 

  Declare @FK Varchar(100) 

  Declare @PK Varchar(100) 

  Declare @PKTABLE Varchar(100) 

  Declare @FKTABLE Varchar(100) 

  Select @PKTABLE=TableName From #temp Where ID=@A 

  Select @PK=Name From Sys.SysColumns Where ID IN(Select ID From Sys.SysObjects Where Name=(Select TableName From #temp 

   Where ID=@A)) and ColStat=1 

  Create Table #fk(ID INT IDENTITY(1,1),TblName Varchar(Max)) 

  Insert Into #fk(TblName) 

  Select name From Sys.SysObjects Where ID IN(Select ID From Sys.SysColumns Where Name=@PK) AND XType='U' 

  Declare @CountTemp INT 

  Declare @AA INT 

  Set @AA=1 

  Select @CountTemp=Count(*) From #fk 

  While(@AA<=@CountTemp) 

  Begin 

      Select @FKTABLE=TblName From #fk Where ID=@AA 

   IF(@FKTABLE<>@PKTABLE)

   Begin

         Set @Sql='ALTER TABLE '+@FKTABLE+' ADD FOREIGN KEY ('+@PK+') REFERENCES '+@PKTABLE+'('+@PK+');' 

         Exec(@Sql)

            Set @Err=@@ERROR         

            if (@ERR <> 0)         

            Begin             

            Raiserror('Insert Statement Fail',16,1)             

            RollBack Transaction Reference               

            End

   End 

      Set @AA=@AA+1 

  End 

  Drop table #fk

  Set @A=@A+1  

End 

Drop table #temp

Commit Tran