Rename Foreign Key Constraints

Question

Rename Foreign Key Constraints

whenriksen

SSCarpal Tunnel

Points: 4752
More actions
April 6, 2013 at 3:57 pm

#270672

Comments posted to this topic are about the item Rename Foreign Key Constraints
Wes
(A solid design is always preferable to a creative workaround)

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

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

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 1

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)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

May 2, 2016 at 3:39 pm

#1876058

Thanks for the script.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

whenriksen (4/8/2013)
Be aware the Print command will only display approximately 8000 characters of text.

Thanks for the reminder.

adelio.stevanato 21159 SSC Enthusiast Points: 194 More actions · Answer 4

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