SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rename Foreign Key Constraints


Rename Foreign Key Constraints

Author
Message
whenriksen
whenriksen
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 693
Comments posted to this topic are about the item Rename Foreign Key Constraints

Wes
(A solid design is always preferable to a creative workaround)
whenriksen
whenriksen
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 693
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)
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36482 Visits: 886
Thanks for the script.
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36482 Visits: 886
whenriksen (4/8/2013)
Be aware the Print command will only display approximately 8000 characters of text.
Thanks for the reminder.
adelio.stevanato 21159
adelio.stevanato 21159
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 60
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search