Drop and Create Foreign keys
This query generates the drop and create statements for foreign keys in SQL Server.
DECLARE
@constraint_name sysname,
@parent_schema sysname,
@parent_name sysname,
@referenced_object_schema sysname,
@referenced_object_name sysname,
@column_name sysname,
@referenced_column_name sysname,
@is_not_for_replication bit,
@is_not_trusted bit,
@delete_referential_action tinyint,
@update_referential_action tinyint,
@AddLine nvarchar(max),
@DropLine nvarchar(max),
@fkline nvarchar(max),
@pkline nvarchar(max),
@object_id int,
@parent_object_id int
DECLARE
@AddScript TABLE (line nvarchar(max))
DECLARE
@DropScript TABLE (line nvarchar(max))
SET NOCOUNT ON
-- Create cursor for foreign keys system view
DECLARE cFKeys CURSOR READ_ONLY
FOR
SELECT object_id,
parent_object_id,
OBJECT_SCHEMA_NAME(parent_object_id),
object_name (parent_object_id),
[name],
is_not_trusted,
OBJECT_SCHEMA_NAME(referenced_object_id),
object_name(referenced_object_id),
delete_referential_action,
update_referential_action,
is_not_for_replication
FROM sys.foreign_keys
OPEN cFKeys
-- Collect basic data
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Start creating command string. One for add and one for drop constraint
SET @AddLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
SET @DropLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
-- Check if it is enabled or not
IF @is_not_trusted = 1
SET @AddLine = @AddLine + N' WITH NOCHECK'
ELSE
SET @AddLine = @AddLine + N' WITH CHECK'
SET @AddLine = @AddLine + N' ADD CONSTRAINT ' + quotename(@constraint_name) + N' FOREIGN KEY ('
SET @DropLine = @DropLine + N' DROP CONSTRAINT ' + quotename(@constraint_name)
-- Gather all columns for current key from foreign key columns system view
DECLARE cColumns CURSOR READ_ONLY
FOR
SELECT fc.name, pc.name
FROM sys.foreign_key_columns fk
inner join sys.columns fc on fk.parent_object_id = fc.object_id and fk.parent_column_id = fc.column_id
inner join sys.columns pc on fk.referenced_object_id = pc.object_id and fk.referenced_column_id = pc.column_id
WHERE parent_object_id = @parent_object_id and fk.constraint_object_id = @object_id
OPEN cColumns
SET @fkline = N''
SET @pkline = N''
FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- One line for column list and one for referenced columns
SET @fkline = @fkline + @column_name
SET @pkline = @pkline + @referenced_column_name
END
FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
IF (@@fetch_status = 0)
BEGIN
SET @fkline = @fkline + ', '
SET @pkline = @pkline + ', '
END
END
CLOSE cColumns
DEALLOCATE cColumns
-- Add column list
SET @AddLine = @AddLine + @fkline + N')' + CHAR(13)
-- Add referenced table and column list
SET @AddLine = @AddLine + 'REFERENCES ' + quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name)
SET @AddLine = @AddLine + N' (' + @pkline + N')'
-- Check the referential action that was declared for this key as well as replication option
SET @AddLine = @AddLine +
' ON DELETE' +
CASE @delete_referential_action
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
' ON UPDATE' +
CASE @UPDATE_REFERENTIAL_ACTION
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
CASE
WHEN @IS_NOT_FOR_REPLICATION = 1 THEN N' NOT FOR REPLICATION'
ELSE N''
END
-- Insert command into table for later use
INSERT INTO @DropScript SELECT @DropLine
INSERT INTO @AddScript SELECT @AddLine
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
END
END
CLOSE cFKeys
DEALLOCATE cFKeys
SET NOCOUNT OFF
SELECT line FROM @DropScript
SELECT line FROM @AddScript
DECLARE
@constraint_name sysname,
@parent_schema sysname,
@parent_name sysname,
@referenced_object_schema sysname,
@referenced_object_name sysname,
@column_name sysname,
@referenced_column_name sysname,
@is_not_for_replication bit,
@is_not_trusted bit,
@delete_referential_action tinyint,
@update_referential_action tinyint,
@AddLine nvarchar(max),
@DropLine nvarchar(max),
@fkline nvarchar(max),
@pkline nvarchar(max),
@object_id int,
@parent_object_id int
DECLARE
@AddScript TABLE (line nvarchar(max))
DECLARE
@DropScript TABLE (line nvarchar(max))
SET NOCOUNT ON
-- Create cursor for foreign keys system view
DECLARE cFKeys CURSOR READ_ONLY
FOR
SELECT object_id,
parent_object_id,
OBJECT_SCHEMA_NAME(parent_object_id),
object_name (parent_object_id),
[name],
is_not_trusted,
OBJECT_SCHEMA_NAME(referenced_object_id),
object_name(referenced_object_id),
delete_referential_action,
update_referential_action,
is_not_for_replication
FROM sys.foreign_keys
OPEN cFKeys
-- Collect basic data
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Start creating command string. One for add and one for drop constraint
SET @AddLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
SET @DropLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
-- Check if it is enabled or not
IF @is_not_trusted = 1
SET @AddLine = @AddLine + N' WITH NOCHECK'
ELSE
SET @AddLine = @AddLine + N' WITH CHECK'
SET @AddLine = @AddLine + N' ADD CONSTRAINT ' + quotename(@constraint_name) + N' FOREIGN KEY ('
SET @DropLine = @DropLine + N' DROP CONSTRAINT ' + quotename(@constraint_name)
-- Gather all columns for current key from foreign key columns system view
DECLARE cColumns CURSOR READ_ONLY
FOR
SELECT fc.name, pc.name
FROM sys.foreign_key_columns fk
inner join sys.columns fc on fk.parent_object_id = fc.object_id and fk.parent_column_id = fc.column_id
inner join sys.columns pc on fk.referenced_object_id = pc.object_id and fk.referenced_column_id = pc.column_id
WHERE parent_object_id = @parent_object_id and fk.constraint_object_id = @object_id
OPEN cColumns
SET @fkline = N''
SET @pkline = N''
FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- One line for column list and one for referenced columns
SET @fkline = @fkline + @column_name
SET @pkline = @pkline + @referenced_column_name
END
FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
IF (@@fetch_status = 0)
BEGIN
SET @fkline = @fkline + ', '
SET @pkline = @pkline + ', '
END
END
CLOSE cColumns
DEALLOCATE cColumns
-- Add column list
SET @AddLine = @AddLine + @fkline + N')' + CHAR(13)
-- Add referenced table and column list
SET @AddLine = @AddLine + 'REFERENCES ' + quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name)
SET @AddLine = @AddLine + N' (' + @pkline + N')'
-- Check the referential action that was declared for this key as well as replication option
SET @AddLine = @AddLine +
' ON DELETE' +
CASE @delete_referential_action
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
' ON UPDATE' +
CASE @UPDATE_REFERENTIAL_ACTION
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
CASE
WHEN @IS_NOT_FOR_REPLICATION = 1 THEN N' NOT FOR REPLICATION'
ELSE N''
END
-- Insert command into table for later use
INSERT INTO @DropScript SELECT @DropLine
INSERT INTO @AddScript SELECT @AddLine
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
END
END
CLOSE cFKeys
DEALLOCATE cFKeys
SET NOCOUNT OFF
SELECT line FROM @DropScript
SELECT line FROM @AddScript
0 Comments:
Post a Comment
<< Home