Rename Foreign Key Constraints

  • Comments posted to this topic are about the item Rename Foreign Key Constraints

    Wes
    (A solid design is always preferable to a creative workaround)

  • Be aware the Print command will only display approximately 8000 characters of text.

    Use the following script if you are scripting foreign keys for the entire database and need to save the output for later deployment:

    DECLARE @SchemaName VARCHAR(50) = NULL;

    DECLARE @TableName VARCHAR(250) = NULL;

    DECLARE @ColumnName VARCHAR(250) = NULL;

    SELECT SQLDrop = 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = Object_ID(N''[' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + FK.name + ']'') AND parent_object_id =OBJECT_ID(N''[' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + ']'') ' + ') BEGIN ALTER TABLE [' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + '] DROP CONSTRAINT [' + FK.name + ']' + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10)

    , SQLAdd = 'BEGIN ' + CHAR(13) + CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT [FK_' + OBJECT_NAME(FK.Parent_object_id) + '_' + KeyOnC.name + '_' + SCHEMA_NAME(ReferencedT.SCHEMA_ID) + ReferencedT.NAME --+ '_' + ReferencedC.name

    + '] FOREIGN KEY(' + KeyOnC.NAME + ') REFERENCES ' + SCHEMA_NAME(ReferencedT.schema_id) + '.' + ReferencedT.NAME + '(' + ReferencedC.NAME + ')'

    --+ ' ON DELETE CASCADE'

    + CHAR(13) + CHAR(10) + 'END'

    , SQLEnable = 'BEGIN ' + CHAR(13) + CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + '] WITH CHECK CHECK CONSTRAINT [FK_' + OBJECT_NAME(FK.Parent_object_id) + '_' + KeyOnC.name + '_' + SCHEMA_NAME(ReferencedT.SCHEMA_ID) + ReferencedT.NAME + ']' --+ '_' + ReferencedC.name

    + CHAR(13) + CHAR(10) + 'END'

    FROM sys.foreign_keys AS FK

    INNER JOIN sys.foreign_key_columns AS FKC

    ON fk.object_id = fkc.constraint_object_id

    INNER JOIN sys.columns AS KeyOnC

    ON FKC.Parent_object_id = KeyOnC.object_id

    AND FKC.Parent_column_id = KeyOnC.column_id

    INNER JOIN sys.columns AS ReferencedC

    ON FKC.referenced_object_id = ReferencedC.object_id

    AND FKC.referenced_column_id = ReferencedC.column_id

    INNER JOIN sys.objects AS KeyOnT

    ON KeyOnC.object_id = KeyOnT.object_id

    INNER JOIN sys.objects AS ReferencedT

    ON ReferencedC.object_id = ReferencedT.object_id

    WHERE ( SCHEMA_NAME(KeyOnT.schema_id) = @SchemaName

    OR @SchemaName IS NULL

    )

    AND ( KeyOnT.name LIKE '%' + @TableName + '%'

    OR ReferencedT.name LIKE '%' + @TableName + '%'

    OR @TableName IS NULL

    )

    AND ( KeyOnC.Name = @ColumnName

    OR ReferencedC.name = @ColumnName

    OR @ColumnName IS NULL

    )

    Wes
    (A solid design is always preferable to a creative workaround)

  • Thanks for the script.

  • whenriksen (4/8/2013)


    Be aware the Print command will only display approximately 8000 characters of text.

    Thanks for the reminder.

  • Please note. Where you have a multi part foreign key that this script will NOT Work correctly.
    it will try and drop and re-create the FK once for each element....
    i.e. in my  case wh have a header table with a branch and document and a detail record with branch,document and seqno

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply