August 19, 2007 at 10:46 pm
I need to drop all foreign key references prior to truncating all the tables and transfering in the data via SSIS from Sybase.
Does anyone have any thoughts on how best to drop the fkeys and dynamically recreating them without hardcoding the create and drop statements?
August 20, 2007 at 7:44 am
I wrote the following a while back when I needed to drop and recreate primary keys. I've just included the foreign key parts.
Warning, this is rough code and needs to be tested very carefully before using in a prod system. I don't think it caters for multi-column foreign keys. It also could use some error handling code.
SET NOCOUNT ON
DECLARE @DropCommand VARCHAR(500), @CreateCommand VARCHAR(500),
@ConstraintName sysname, @ColumnName sysname
CREATE TABLE #TablesToMove (
TableName sysname
)
INSERT INTO #TablesToMove
...... -- Insert into this table the names of the tables you need to affect.
CREATE TABLE #Instructions (
ExecOrder INT IDENTITY,
Command VARCHAR(500)
)
-- drop the foreign keys and log the recreation code.
DECLARE curForeignKeys CURSOR LOCAL FAST_FORWARD FOR
SELECT
'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' + ParentColumns.name + ') REFERENCES ' + OBJECT_NAME(fk.referenced_object_id) + ' (' + ReferencedColumns.name + ')' + CASE delete_referential_action WHEN 1 THEN ' ON DELETE CASCADE' ELSE '' END AS CreationCommand,
'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' DROP CONSTRAINT ' + fk.name AS DropCommand
FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns ParentColumns ON fkc.parent_object_id = ParentColumns.OBJECT_ID AND fkc.parent_column_id = ParentColumns.column_id
INNER JOIN sys.columns ReferencedColumns ON fk.referenced_object_id = ReferencedColumns.object_id AND fkc.referenced_column_id = ReferencedColumns.column_id
WHERE OBJECT_NAME(fk.referenced_object_id) IN (SELECT TableName FROM #TablesToMove)
ORDER BY fk.referenced_object_id, fk.parent_object_id
OPEN curForeignKeys
FETCH NEXT FROM curForeignKeys INTO @CreateCommand, @DropCommand
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DropCommand
EXECUTE (@DropCommand)
INSERT INTO #Instructions (Command)
VALUES (@CreateCommand)
FETCH NEXT FROM curForeignKeys INTO @CreateCommand, @DropCommand
END
CLOSE curForeignKeys
DEALLOCATE curForeignKeys
-- do whatever needs to be done here.
DECLARE curRecreate CURSOR LOCAL FAST_FORWARD FOR
SELECT Command FROM #Instructions ORDER BY ExecOrder DESC
OPEN curRecreate
FETCH next FROM curRecreate INTO @CreateCommand
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @CreateCommand
EXECUTE (@CreateCommand)
FETCH next FROM curRecreate INTO @CreateCommand
END
CLOSE curRecreate
DEALLOCATE curRecreate
DROP TABLE #TablesToMove
DROP TABLE #Instructions
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply