Sync Foreign Keys In Sql Server 2005

List all foreigh keys in sql 2005
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName


FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Order By f.name , OBJECT_NAME(f.parent_object_id),fc.constraint_column_id


Sync foreigh key in sql 2005



BEGIN TRAN
BEGIN TRY

set nocount on

Declare @idoc int
set @Error_Code=0
set @Error_Desc=''

Declare @ConstraintTable Table
(
Sr_No Int Identity(1,1),
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
exec sp_xml_preparedocument @idoc output , @ConstraintsXML
Insert Into @ConstraintTable
Select ForeignKey,
TableName,
ColumnName,
ReferenceTableName,
ReferenceColumnName
From OpenXML(@idoc,'/NewDataSet/Table',2)
With (
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
Order By ForeignKey,TableName
exec sp_xml_removedocument @idoc

Declare @ForeignKey Varchar(Max)
Declare @TableName Varchar(Max)
Declare @ColumnName Varchar(Max)
Declare @ReferenceTableName Varchar(Max)
Declare @ReferenceColumnName Varchar(Max)

Declare @CTableName Varchar(Max)
Declare @CColumnName Varchar(Max)
Declare @CReferenceTableName Varchar(Max)
Declare @CReferenceColumnName Varchar(Max)

Declare @SrNo Int
Declare @ForeignKeyString Varchar(Max)
Declare @Rows Int
Declare @CommandStr Varchar(Max)

Set @SrNo = 1
Set @ForeignKeyString = ''
Select @Rows = Count(*) From @ConstraintTable
--Select * From @ConstraintTable

Set @CTableName = ''
Set @CColumnName = ''
Set @CReferenceTableName = ''
Set @CReferenceColumnName = ''

Select * From @ConstraintTable


While @SrNo < = @Rows + 1
Begin
Select @ForeignKey = ForeignKey,
@TableName = TableName,
@ColumnName = ColumnName,
@ReferenceTableName = ReferenceTableName,
@ReferenceColumnName = ReferenceColumnName
From @ConstraintTable
Where Sr_No = @SrNo

--print @ForeignKey
--print @SrNo


IF (@ForeignKeyString = @ForeignKey) And @SrNo <= @Rows
Begin --Same Key Append Column Names
set @CColumnName = @CColumnName + ',' + @ColumnName
set @CReferenceColumnName = @CReferenceColumnName + ',' + @ReferenceColumnName
End --Same Key Append Column Names
Else
Begin --Another Foreigh Key Make new command

If Len(@ForeignKeyString)>0
Begin



Set @CommandStr = 'If Exists( select * FROM sys.foreign_keys Where Name=' + CHAR(39) + @ForeignKeyString + Char(39) +') ALTER TABLE '
Set @CommandStr = @CommandStr + @CTableName + ' DROP CONSTRAINT [' + @ForeignKeyString +']'

--print @CommandStr
exec (@CommandStr)

Set @CommandStr = ' ALTER TABLE ' + @CTableName
Set @CommandStr = @CommandStr + ' WITH NOCHECK ADD CONSTRAINT [' + @ForeignKeyString + ']'
Set @CommandStr = @CommandStr + ' FOREIGN KEY (' + @CColumnName + ') '
Set @CommandStr = @CommandStr + ' REFERENCES ' + @CReferenceTableName + '(' + @CReferenceColumnName + ')'

--print @CommandStr
exec (@CommandStr)

End

Set @ForeignKeyString = @ForeignKey
Set @CTableName = @TableName
Set @CColumnName = @ColumnName
Set @CReferenceTableName = @ReferenceTableName
Set @CReferenceColumnName = @ReferenceColumnName



End --Another Foreigh Key Make new command


Set @SrNo = @SrNo + 1
End



COMMIT TRAN
print 'Sync Successfull !'
END TRY

BEGIN CATCH
ROLLBACK TRAN
Select @Error_Code = cast(ERROR_NUMBER() as varchar(5))
Select @Error_Desc = @CommandStr + char(13) + CAST(ERROR_LINE() AS VARCHAR(4000)) + 'Ö' + CAST(ERROR_MESSAGE() as VARCHAR(4000)) /*+ 'll'+ @query*/
print @Error_Code
print @Error_Desc
END CATCH