SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE for a specific Schema

Question

I have a lot of Foreign Key Columns with cascade delete set to NO ACTION in some specific Schema and I want to Set the FK Columns to ON DELETE SET NULL (for Example) where Primary Key Column's Schema relating to this Foreign Key Column is starting with 'Store'.
I found this article in GitHub 2735f6b4c3c43420cfb1 but unfortunately I was unable to edit it to fit my needs,
And Also I was Expecting the code to work for both Single & Multiple Columns which is not the case, can somebody help...

Answer 1

Hi @Jin Ka

I want to Set the FK Columns to ON DELETE SET NULL in Only (for Example) any Schema name starting with 'Store'

How about add a filter in the where clause, like this:

 where (sys.objects.[type] = 'U')  
   and (sys.objects.[name] not in ('sysdiagrams'))  
   and schema_name(sys.objects.schema_id) like 'Store%'  

work for both Single & Multiple Columns

Refer to the second answer by Ed Avis in this thread: SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.