[ACCEPTED]-SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE-sql-server-2005

Here's a script I used for a similiar purpose. It 4 does not support composite foreign keys 3 (which use more than one field.) And it 2 would probably need some tweaking before 1 it will work for your situation. EDIT: In particular it does not handle multi-column foreign keys correctly.

select
  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
,  CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + 
      '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + 
      ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  sys.objects.[name] + ']([' +
  sys.columns.[name] + ']) ON DELETE CASCADE; '
 from sys.objects
  inner join sys.columns
    on (sys.columns.[object_id] = sys.objects.[object_id])
  inner join (
    select sys.foreign_keys.[name] as ForeignKeyName
     ,schema_name(sys.objects.schema_id) as ForeignTableSchema
     ,sys.objects.[name] as ForeignTableName
     ,sys.columns.[name]  as ForeignTableColumn
     ,sys.foreign_keys.referenced_object_id as referenced_object_id
     ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
     from sys.foreign_keys
      inner join sys.foreign_key_columns
        on (sys.foreign_key_columns.constraint_object_id
          = sys.foreign_keys.[object_id])
      inner join sys.objects
        on (sys.objects.[object_id]
          = sys.foreign_keys.parent_object_id)
        inner join sys.columns
          on (sys.columns.[object_id]
            = sys.objects.[object_id])
           and (sys.columns.column_id
            = sys.foreign_key_columns.parent_column_id)
    ) ForeignKeys
    on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
     and (ForeignKeys.referenced_column_id = sys.columns.column_id)
 where (sys.objects.[type] = 'U')
  and (sys.objects.[name] not in ('sysdiagrams'))

Andomar's answer above is good but works for single-column 8 foreign key constraints only. I adapted 7 it a little for multi-column constraints:

create function dbo.fk_columns (@constraint_object_id int)
returns varchar(255)
as begin
declare @r varchar(255)
select @r = coalesce(@r + ',', '') + c.name
from sys.foreign_key_columns fkc
join sys.columns c
  on  fkc.parent_object_id = c.object_id
  and fkc.parent_column_id = c.column_id
where fkc.constraint_object_id = @constraint_object_id
return @r
end

select distinct
  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
      '].[' + ForeignKeys.ForeignTableName +
      '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + '] '
,  CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
  '].[' + ForeignKeys.ForeignTableName +
  '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName +
  '] FOREIGN KEY(' + dbo.fk_columns(constraint_object_id) + ')' +
  'REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  sys.objects.[name] + '] '
  + ' ON DELETE CASCADE'
 from sys.objects
  inner join sys.columns
    on (sys.columns.[object_id] = sys.objects.[object_id])
  inner join (
select sys.foreign_keys.[name] as ForeignKeyName
 ,schema_name(sys.objects.schema_id) as ForeignTableSchema
 ,sys.objects.[name] as ForeignTableName
 ,sys.columns.[name]  as ForeignTableColumn
 ,sys.foreign_keys.referenced_object_id as referenced_object_id
 ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
 ,sys.foreign_keys.object_id as constraint_object_id
 from sys.foreign_keys
  inner join sys.foreign_key_columns
    on (sys.foreign_key_columns.constraint_object_id
      = sys.foreign_keys.[object_id])
  inner join sys.objects
    on (sys.objects.[object_id]
      = sys.foreign_keys.parent_object_id)
    inner join sys.columns
      on (sys.columns.[object_id]
        = sys.objects.[object_id])
       and (sys.columns.column_id
        = sys.foreign_key_columns.parent_column_id)
-- Uncomment this if you want to include only FKs that already
-- have a cascade constraint.
--       where (delete_referential_action_desc = 'CASCADE' or update_referential_action_desc = 'CASCADE')
) ForeignKeys
on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
 and (ForeignKeys.referenced_column_id = sys.columns.column_id)
 where (sys.objects.[type] = 'U')
  and (sys.objects.[name] not in ('sysdiagrams'))

You 6 can also use the query to help remove ON DELETE CASCADE from FKs 5 that currently have it.

This still doesn't 4 handle the case where the columns are named 3 different things in the two tables - another 2 user-defined function would need to be defined 1 for that.

More Standards compliant solution:

;WITH CTE AS 
(
    SELECT  
         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
        ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME 
        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
        ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME 
        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)


SELECT 
     FK_CONSTRAINT_NAME
    --,FK_SCHEMA_NAME
    --,FK_TABLE_NAME
    --,FK_COLUMN_NAME
    --,FK_ORDINAL_POSITION
    --,REFERENCED_CONSTRAINT_NAME
    --,REFERENCED_SCHEMA_NAME
    --,REFERENCED_TABLE_NAME
    --,REFERENCED_COLUMN_NAME
    --,REFERENCED_ORDINAL_POSITION

    ,
        'ALTER TABLE [' + FK_SCHEMA_NAME + ']' 
        + '.[' + FK_TABLE_NAME + '] ' 
        + 'DROP CONSTRAINT [' + FK_CONSTRAINT_NAME + ']; ' 
    AS DropStmt 

    ,
    'ALTER TABLE [' + FK_SCHEMA_NAME + ']' 
        + '.[' + FK_TABLE_NAME + '] ' + 
        + 'WITH CHECK ADD CONSTRAINT [' +  FK_CONSTRAINT_NAME + '] ' 
        + 'FOREIGN KEY([' + FK_COLUMN_NAME + ']) ' 
        + 'REFERENCES [' + REFERENCED_SCHEMA_NAME + '].[' + REFERENCED_TABLE_NAME + ']([' + REFERENCED_COLUMN_NAME + ']) ON DELETE CASCADE; ' 
    AS CreateStmt 

FROM CTE 

WHERE (1=1) 
/*
AND FK_TABLE_NAME IN 
(
     'T_SYS_Geschossrechte'
    ,'T_SYS_Gebaeuderechte'
    ,'T_SYS_Standortrechte'
)

AND REFERENCED_TABLE_NAME NOT LIKE 'T_AP_Ref_Mandant'
*/

ORDER BY  
     FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME
    ,FK_COLUMN_NAME
    ,FK_ORDINAL_POSITION
    ,REFERENCED_CONSTRAINT_NAME
    ,REFERENCED_TABLE_NAME
    ,REFERENCED_COLUMN_NAME
    ,REFERENCED_ORDINAL_POSITION

Edit:
Extended 2 for multi-column foreign-keys:

;WITH CTE AS 
( 
  SELECT  
         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
        ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME 
        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
        ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME 
        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)
SELECT 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 
    --,FK_COLUMN_NAME
    --,REFERENCED_COLUMN_NAME



    ,
    'ALTER TABLE ' + QUOTENAME(FK_SCHEMA_NAME) + '.' + QUOTENAME(FK_TABLE_NAME) + ' ' 
      + 'DROP CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + '; ' 
    AS DropStmt 

    ,
    'ALTER TABLE ' + QUOTENAME(FK_SCHEMA_NAME) + '.' + QUOTENAME(FK_TABLE_NAME) + ' 
    ADD CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + ' 
    FOREIGN KEY('
    + 
        SUBSTRING 
        ( 
            (
                SELECT ', ' + QUOTENAME(FK.FK_COLUMN_NAME) AS [text()] 
                FROM CTE AS FK 
                WHERE FK.FK_CONSTRAINT_NAME = CTE.FK_CONSTRAINT_NAME
                AND FK.FK_SCHEMA_NAME = CTE.FK_SCHEMA_NAME
                AND FK.FK_TABLE_NAME = CTE.FK_TABLE_NAME 
                ORDER BY FK.FK_ORDINAL_POSITION
                FOR XML PATH, TYPE 
            ).value('.[1]', 'nvarchar(MAX)')
            ,3, 4000
        ) 
    + ') 
'
    + '    REFERENCES ' + QUOTENAME(REFERENCED_SCHEMA_NAME) + '.' + QUOTENAME(REFERENCED_TABLE_NAME) + '(' 
    + SUBSTRING 
      ( 
          (
              SELECT ', ' + QUOTENAME(Referenced.REFERENCED_COLUMN_NAME) AS [text()] 
              FROM CTE AS Referenced 
              WHERE Referenced.FK_CONSTRAINT_NAME = CTE.FK_CONSTRAINT_NAME
              AND Referenced.REFERENCED_SCHEMA_NAME = CTE.REFERENCED_SCHEMA_NAME
              AND Referenced.REFERENCED_TABLE_NAME = CTE.REFERENCED_TABLE_NAME
              ORDER BY Referenced.REFERENCED_ORDINAL_POSITION 
              FOR XML PATH, TYPE 
          ).value('.[1]', 'nvarchar(MAX)')
          , 3, 4000 
      )
    + ') 
    ON DELETE CASCADE 
; ' AS CreateStmt 

FROM CTE 

GROUP BY 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 

    ,REFERENCED_SCHEMA_NAME
    ,REFERENCED_TABLE_NAME

And the far 1 simpler version for PostGreSQL:

;WITH CTE AS 
( 
  SELECT  
         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
        ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME 
        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
        ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME 
        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)
SELECT 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 
    --,FK_COLUMN_NAME
    --,REFERENCED_COLUMN_NAME



    ,
    'ALTER TABLE ' || QUOTE_IDENT(FK_SCHEMA_NAME) || '.' || QUOTE_IDENT(FK_TABLE_NAME) || ' ' 
      || 'DROP CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || '; ' 
    AS DropStmt 

    ,
    'ALTER TABLE ' || QUOTE_IDENT(FK_SCHEMA_NAME) || '.' || QUOTE_IDENT(FK_TABLE_NAME) || ' 
    ADD CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || ' 
    FOREIGN KEY(' || string_agg(FK_COLUMN_NAME, ', ') || ') 
'
    || '    REFERENCES ' || QUOTE_IDENT(REFERENCED_SCHEMA_NAME) || '.' || QUOTE_IDENT(REFERENCED_TABLE_NAME) || '(' || string_agg(REFERENCED_COLUMN_NAME, ', ') || ') 
    ON DELETE CASCADE 
; ' AS CreateStmt 

FROM CTE 

GROUP BY 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 

    ,REFERENCED_SCHEMA_NAME
    ,REFERENCED_TABLE_NAME

You'll have to to alter the table, drop 9 FK constraints and recreate them:

This is 8 db2 syntax. SQLServer should be similar

ALTER TABLE emp DROP CONSTRAINT fk_dept;

ALTER TABLE emp ADD CONSTRAINT fk_dept
FOREIGN KEY(dept_no)
REFERENCES dept(deptno)
ON DELETE CASCADE;

You 7 can write your own sp to query the system 6 table for all the foreign keys, drop them 5 and recreate them. You'll have to use dynamic 4 sql in your sp to do this where you can 3 loop through the fk defn, put them in a 2 varchar and append/edit to include CASCADE 1 and then execute stmt.

The answer from @Andomar worked for me but it was 8 a bit manual - you have to run it, then 7 copy the results and run them. I needed 6 to use this as part of my automated test 5 setup so needed it to run in one query automatically.

I 4 have come up with the following which gets 3 all the SQL to run in order to modify the 2 foreign key constraints, then actually runs 1 it all in one go:

    IF Object_id('tempdb..#queriesForContraints') IS NOT NULL 
      BEGIN 
          DROP TABLE #queriesForContraints
      END 

    DECLARE @ignoreTablesCommaSeparated VARCHAR(1000)

    SELECT 'ALTER TABLE [' 
           + ForeignKeys.foreigntableschema + '].[' 
           + ForeignKeys.foreigntablename 
           + '] DROP CONSTRAINT [' 
           + ForeignKeys.foreignkeyname + ']; ' 
           + 'ALTER TABLE [' 
           + ForeignKeys.foreigntableschema + '].[' 
           + ForeignKeys.foreigntablename 
           + '] WITH CHECK ADD CONSTRAINT [' 
           + ForeignKeys.foreignkeyname 
           + '] FOREIGN KEY([' 
           + ForeignKeys.foreigntablecolumn 
           + ']) REFERENCES [' 
           + Schema_name(sys.objects.schema_id) + '].[' 
           + sys.objects.[name] + ']([' 
           + sys.columns.[name] 
           + ']) ON DELETE CASCADE; ' AS query
    INTO #queriesForContraints
    FROM   sys.objects 
           INNER JOIN sys.columns 
                   ON ( sys.columns.[object_id] = sys.objects.[object_id] ) 
           INNER JOIN (SELECT sys.foreign_keys.[name]                      AS 
                              ForeignKeyName, 
                              Schema_name(sys.objects.schema_id)           AS 
                  ForeignTableSchema, 
                              sys.objects.[name]                           AS 
                  ForeignTableName, 
                              sys.columns.[name]                           AS 
                  ForeignTableColumn, 
                              sys.foreign_keys.referenced_object_id        AS 
                  referenced_object_id, 
                              sys.foreign_key_columns.referenced_column_id AS 
                  referenced_column_id 
                       FROM   sys.foreign_keys 
                              INNER JOIN sys.foreign_key_columns 
                                      ON ( 
                              sys.foreign_key_columns.constraint_object_id = 
                              sys.foreign_keys.[object_id] ) 
                              INNER JOIN sys.objects 
                                      ON ( sys.objects.[object_id] = 
                                         sys.foreign_keys.parent_object_id ) 
                              INNER JOIN sys.columns 
                                      ON ( sys.columns.[object_id] = 
                                           sys.objects.[object_id] ) 
                                         AND ( sys.columns.column_id = 
    sys.foreign_key_columns.parent_column_id )) 
    ForeignKeys 
    ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] ) 
    AND ( ForeignKeys.referenced_column_id = sys.columns.column_id ) 
    WHERE  ( sys.objects.[type] = 'U' ) 
           AND ( sys.objects.[name] NOT IN ( 
                'sysdiagrams' --add more comma separated table names here if required
            ) ) 

    DECLARE @queryToRun NVARCHAR(MAX)

    SELECT  @queryToRun = STUFF(
            (SELECT query + ''
             FROM #queriesForContraints
             FOR XML PATH (''))
         , 1, 0, '')

    EXEC sp_executesql @statement = @queryToRun

    IF Object_id('tempdb..#queriesForContraints') IS NOT NULL 
      BEGIN 
          DROP TABLE #queriesForContraints
      END 

A simple solution I found is to export the 5 database to a single file, use the search 4 function to replace all the NO ACTION to 3 CASCADE, drop the database and import the 2 edited file.

Information changed on the database 1 between the export and import will be lost.