Preview SQL DELETE for records which have ON CASCADE constraints

Question

We have a master 'users' table and many tables that refer to the UserId either

• Directly: UserId is a FK in the child table OR
• Indirectly: another FK in the 'grandchild' table that references a record in the child table, that in turn refers to UserID via a FK constraint.

Now if everything is perfect, deleting a user should be as straightforward as deleting them in the master table and the ON CASCADE constraints rippling it thru the rest of the tables. The issue is that we're not 100% sure if every FK relationship in every table referenced (directly or indirectly) has the ON CASCADE constraint. We need some way to issue that delete and watch which tables SQL server actually touches to delete. I read this and tried it but it doesn't display any tables cascaded into - just the entries in the master table only

Here is what I tried:

DELETE umt
OUTPUT DELETED.*
FROM [OurAppDb].[dbo].[UserMasterTable] umt
WHERE umt.UserId LIKE 'ABCDABCD-ABCD-ABCD-ABCD-ABCDABCDABCD'

How can I see all the tables the above query would touch?

NOTE: The ON CASCADE constraint is a constraint in the database that we think we added for every table when each table was built. Example of it being added on one table

ALTER TABLE [dbo].[UserEmailPrefs]  
WITH CHECK ADD  CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserMasterTable] ([UserId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserEmailPrefs] CHECK CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId]
GO

Answer 1

To inspect the referential constraints in your entire database that reference the UserMasterTable, use INFORMATION_SCHEMA views.

SELECT RC.CONSTRAINT_NAME, TU.TABLE_NAME, RC.DELETE_RULE, RC.UPDATE_RULE 
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
  INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE TU
          ON RC.CONSTRAINT_CATALOG = TU.CONSTRAINT_CATALOG 
         AND RC.CONSTRAINT_NAME = TU.CONSTRAINT_NAME
  INNER JOIN INFORMATION_SCHEMA.table_constraints TC 
          ON RC.unique_constraint_name = TC.CONSTRAINT_NAME
  WHERE TC.TABLE_NAME='Users'

This returns a list of the referential constraints targeting UserMasterTable, and for each one, which table is referencing UserMasterTable, and what the ON DELETE and ON UPDATE rules are. From this you can quickly see which referential constraints are lacking the desired CASCADE rule. No need to get trigger-happy.

To extend this to "grandchild" references, add two more join clauses. To extend it to any number of levels, go for a recursive CTE.

Answer 2

I think you can do this in trigger(analyse [inserted] table and search for subordinated tables). As example: you can create table which will store queries for detecting Foreign Key links :

IF NOT EXISTS (
        SELECT *
        FROM [sys].tables T
        WHERE T.NAME = 'FKCheck'
        )
    CREATE TABLE FKCheck (
        TableName SYSNAME
        ,ChildTable SYSNAME
        ,QueryText NVARCHAR(MAX)
        )
ELSE
    EXEC('DROP TABLE FKCheck')

Then fill it with dynamic query extracted from metadata

;WITH CTE_FKs
AS (
    SELECT FK.NAME
        ,OBJECT_name(fk.parent_object_id) AS ChildTable
        ,OBJECT_name(fk.referenced_object_id) AS ParentTable
        ,delete_referential_action_desc AS DeleteAction
        ,MainTable.NAME AS MainTableColumn
        ,ChildObject.NAME AS ChildColumnName
    FROM sys.foreign_keys FK
    INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id
    INNER JOIN sys.columns AS ChildObject ON ChildObject.object_id = FKc.parent_object_id
        AND FKC.parent_column_id = ChildObject.column_id
    INNER JOIN sys.columns AS MainTable ON MainTable.object_id = FK.referenced_object_id
        AND MainTable.column_id = FKC.referenced_column_id
    )
    ,CTE_Tables
AS (
    SELECT DISTINCT C.NAME
        ,C.ParentTable
        ,C.DeleteAction
        ,C.ChildTable
    FROM [CTE_FKs] C
    )
INSERT INTO [dbo].[FKCheck] (
    TableName
    ,ChildTable
    ,QueryText

    )
SELECT C.ParentTable,C.ChildTable
    ,'IF EXISTS (select 1 from inserted INNER JOIN  ' + QUOTENAME(C.ChildTable) + ' ON ' + STUFF((
            SELECT ' AND inserted.' + QUOTENAME(C2.MainTableColumn) + ' = ' + + QUOTENAME(C2.ChildTable) + '.' + QUOTENAME(C2.ChildColumnName)
            FROM CTE_FKs C2
            WHERE C2.ParentTable = C.ParentTable
                AND C2.NAME = C.NAME
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'nvarchar(MAX)'), 1, 4, '') + ')
 RAISERROR(''Relation with ' + QUOTENAME(C.ChildTable) +':'+ CASE C.DeleteAction
        WHEN 'CASCADE'
            THEN ' data will be deleted'
        WHEN 'SET_NULL'
            THEN ' set as NULL'
        WHEN 'NO_ACTION'
            THEN ' no default action'
        ELSE 'Unknown'
        END + ''')'
FROM [CTE_Tables] C

Your query in table will be looking like:

 IF EXISTS (select 1 from inserted INNER JOIN  [UserEmailPrefs] ON  inserted.[UserId] = [UserEmailPrefs].[UserId])
 RAISERROR('Relation with [UserEmailPrefs]: no default action')
IF EXISTS (select 1 from inserted INNER JOIN  [UserEmail] ON  inserted.[UserId] = [UserEmail].[UserId])
 RAISERROR('Relation with [UserEmail]: set as NULL')

Then in trigger you can execute query to print message:

 DECLARE @TableName SYSNAME = 'UserMasterTable';
    DECLARE @sSQL NVARCHAR(MAX) = '';

    SELECT @sSQL += F.QueryText + CHAR(10)
    FROM FKCheck F
    WHERE F.TableName = @TableName;
EXEC(@sSQL)
ROLLBACK

If you need to analyze more "distant" tables, you need to walk through hierarchy in FKCheck table.