MS SQL alter all constraints

Question

In order to alter a constraint in MS SQL one usually drops the old constraint and adds a new one with the desired properties, like that:

alter table MY_TABLE drop constraint [MY_CONSTRAINT]
go

alter table MY_TABLE
    add constraint [MY_CONSTRAINT]
        foreign key (FK_TABLE_2) references TABLE_2
            on delete set null
        --or whatever property we want to change
go

However, I want to change all the constraints in my database. More specifically, I want all my foreign key constraints to have the delete rule set null. Is something like this possible?

Answer 1

If I wanted to do this, I'd do it with Powershell/SMO. Something like this:

import-module sqlps;

$scriptDrop = new-object microsoft.sqlserver.management.smo.scriptingoptions;
$scriptDrop.ScriptDrops = $true;
$s = new-object microsoft.sqlserver.management.smo.server '.';
$db = $s.Databases['yourDatabase'];
foreach ($table in $db.Tables) {
  foreach ($fk in $table.ForeignKeys) {
    $fk.DeleteAction = 'SetNull';
    $fk.Script($scriptDrop);
    $fk.Script();
  }
}

This should generate a script that will drop and subsequently re-create the foreign keys with the desired property.

Answer 2

I don't know about something that allows to do it all at once, but here is a select statement that might make you gain a bit of time:

SELECT 'ALTER TABLE [' + o1.name + '] DROP CONSTRAINT [' + fk.name + ']' AS DROP_STATEMENT
    ,   'ALTER TABLE [' + o1.name +'] ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY ([' + c.name + ']) REFERENCES [' + o2.name + '] ON DELETE SET NULL' AS CREATE_STATEMENT
FROM sys.foreign_keys fk
    INNER JOIN sys.objects o1 ON fk.parent_object_id = o1.object_id
    INNER JOIN sys.objects o2 ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON c.object_id = o2.object_id AND c.column_id = fkc.referenced_column_id

As pointed out in the comments, this won't work at all when the fk is referencing two or more columns. However as I don't think I can adapt this query easily and I don't know what the value for you would be ; I won't. Just keep this in mind.