Add Foreign Keys Back to the Database

Thu Mar 1, 2007 by Brett Kaiser (x002548) in sql-gimmicks

"OK Brett, Now that I Removed all my Foreign Keys to Truncate the Data, Now What? I'm Hosed! Thanks a bunch"

OK, Well....sorry to keep you hangng out there. But, if you followed the code in the above link you will have all of the RI saved to the work table, so now all you need to do is replay it. The following is the code that will do this for you. Again, sorry for the delay.

CREATE procedure isp_exec_FK_code

AS

DECLARE @FKcode nvarchar(3000)
DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int
SET NOCOUNT ON
SELECT @rc = 0
DECLARE FKcode cursor fast_forward read_only for

SELECT * FROM FK_Create_code

OPEN FKcode

FETCH NEXT FROM FKcode
INTO @FKcode

WHILE @@fetch_status = 0
BEGIN
execute sp_executesql @FKcode
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_exec_FK_code_Error
  END
FETCH NEXT FROM FKcode
INTO @FKcode
END

isp_exec_FK_code_Exit:
CLOSE FKcode
DEALLOCATE FKcode
SET NOCOUNT OFF
RETURN @rc

isp_exec_FK_code_Error:

If @Error_Type = 50001
BEGIN
  Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
          + ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
          + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
          + ',"' + ' Message: ' + ',"' + RTrim(description)
          From master..sysmessages
        Where error = @error_out)
END

RAISERROR @Error_Type @Error_Message

GOTO isp_exec_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO