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

[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%'

 

[Pat Hartman]

I question the logic of the change. You are deliberately creating orphans. This will only work if the FK on the child record is set to be not required. It is rare that FK's are optional. One example would be a motor pool. The pool contains all the vehicles owned by the company. It is possible for the FK for the AssignedToUserID to be null and that means the vehicle is available to be assigned permanently or temporarily to a user. However, once a vehicle is assigned to a user, RI is enforced. So, if an employee left the company, you would want to sever the link to any vehicles which were assigned to him. Normally, this would be done one by one since it is unlikely the app would actually delete an employee. This cascade to null would happen if an employee were deleted. So, as I said, this is a very rare occurrence.

Although this is a useful option and ACE does support it, it doesn't support it through the GUI so most Access developers will not be aware of what it does. To set this value, you need to use DAO or possibly ADO. I've never used it with Jet/ACE so I don't know if the feature is available to both object models.