How to make an SQL Script to alter ALL Foreign Keys to add ON DELETE SET NULL for some specific Schema

[Huska]

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 some codes like this one below but unfortunately I was unable to add the TSQL needed to fit my needs, can somebody help...

 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

 

[theDBguy]

Hi. No need to post duplicate questions. I'll be deleting this one shortly.

 

[theDBguy]

As requested by the OP, I am deleting the other thread instead.

 

[cheekybuddha]

(FYI: Cross-posted at UA)

 

[Huska]

@cheekybuddha
I am In Need of an Answer and In that site I waited a lot and found nobody to answer my question, that's why I am New to AWF looking for solutions, by the way Can you find anybody to Answer that? If no then I will look for solution in some Other Active Forumns . . .

 

[sonic8]

I am In Need of an Answer

It feels more like you are in need for someone writing your code.

I would first try to add a criterion to the where condition of the outer query:

...

WHERE  ( sys.objects.[type] = 'U' )
           AND ( sys.objects.[name] NOT IN (
                'sysdiagrams' --add more comma separated table names here if required
            ) )
AND Schema_name(sys.objects.schema_id)  LIKE 'store%'