Help W/Disabling FK Constraints for Batch Operations
HI all,
I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.
Here's where I'm at:
-
CREATE PROCEDURE [dbo].[_InitializeDB]
AS
SET NOCOUNT ON
DECLARE @name varchar(255)
DECLARE @sql nvarchar(255)
DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'
OPEN tables
FETCH NEXT FROM tables INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = 'ALTER TABLE ['+ @name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @sql
SET @sql = 'DISABLE TRIGGER ALL ON [' + @name + ']'
EXEC sp_executeSQL @sql
SET @sql = 'TRUNCATE TABLE [' + @name + ']'
EXEC sp_executesql @sql
BEGIN TRY
SET @sql = 'INSERT INTO [' + @name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @sql
END TRY
BEGIN CATCH
PRINT @sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @sql = 'ENABLE TRIGGER ALL ON [' + @name + ']'
EXEC sp_executeSQL @sql
SET @sql = 'ALTER TABLE ['+ @name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @sql
FETCH NEXT FROM tables INTO @name
END
CLOSE tables
DEALLOCATE tables
-
Running this Sproc produces (for the first ref'd table):
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.
