x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Thursday, March 01, 2007

Add Foreign Keys Back to the Database

"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

 

 

posted @ Thursday, March 01, 2007 9:49 AM | Feedback (0) | Filed Under [ SQL Gimmicks ]

Powered by:
Powered By Subtext Powered By ASP.NET