Sql server - recursive delete

Question

I'm trying to delete user's data and all it's related data that is located in different tables. All the tables have Foreign Keys but without cascade delete.

I investigated some options:

1. Enable cascade delete on all FK, delete and remove the cascade delete.
2. Delete from bottom UP, loop up for all the leaves delete and repeat this operation till Root.

Are there any more smart option or other Techniques?

I'm using Microsoft SQL Server 2012 (SP1)

Solution 1

Oracle solution:

How to generate DELETE statements in PL/SQL, based on the tables FK relations?

SQL Server solution:

Generate Delete Statement From Foreign Key Relationships in SQL 2008?

Hope it helps

Solution 2

Those are the best and most efficient ones. For production queries I would use 2.

The only other ways I can think of would (IMO) only be suitable for quick and dirty removal of data in a test environment (avoiding the need to analyse the correct order)

1. Disable all FKs delete the desired data then re-enable the FKs. This is inefficient as they need to be re-enabled WITH CHECK to avoid leaving the FKs in an untrusted state which means that all preserved data needs to be re-validated.
2. List out all DELETE statements on affected tables in arbitrary order and run the batch as many times as necessary until it succeeds with no FK errors.

Solution 3

Cascade delete is easy, well-performing and reliable. Works over multiple levels. The query plans are interesting and seem well-optimized.

If you want manual deletes, make sure to issue just one query per table for efficiency reasons. You can use joins in a delete statement to join to the parent level(s) to filter the rows to be deleted.

Solution 4

I found a nice solution about it, under:How to generate DELETE statements in PL/SQL, based on the tables FK relations?

generate the function:

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
    DROP FUNCTION dbo.udfGetFullQualName;

GO
CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
    DECLARE @schema_id AS BIGINT;
    SELECT @schema_id = schema_id
    FROM   sys.tables
    WHERE  object_id = @ObjectId;
    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END

GO
--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
    DROP FUNCTION dbo.udfGetOnJoinClause;

GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
    DECLARE @OnClauseTemplate AS VARCHAR (1000);
    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
    DECLARE @str AS VARCHAR (1000);
    SET @str = '';
    SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM   dbo.sysforeignkeys AS fk
    WHERE  fk.constid = @fkNameId; --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END

GO
--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
    DROP PROCEDURE dbo.uspCascadeDelete;

GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='N', --'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR (8000)='', @Level INT=0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP,  IF LEVEL 0, THEN KEEP DEFAULT
AS -- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys
SET NOCOUNT ON;
/* Set up debug */
DECLARE @DebugMsg AS VARCHAR (4000), 
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
    BEGIN -- assume owner is dbo and calculate id
        IF CHARINDEX('.', @ParentTableId) = 0
            SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
        ELSE
            SET @ParentTableId = OBJECT_ID(@ParentTableId);
    END
IF @Level = 0
    BEGIN
        PRINT @DebugIndent + ' **************************************************************************';
        PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
        IF @ExecuteDelete = 'Y'
            PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
        ELSE
            PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
    END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
    SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
    SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
    EXECUTE (@strSQL);
ELSE
    PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT constid AS fkNameId, -- constraint name
                        fkeyid AS cTableId
        FROM   dbo.sysforeignkeys AS fk
        WHERE  fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
               AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT, 
@cTableId AS INT, 
@cColId AS INT, 
@pTableId AS INT, 
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
    BEGIN
        SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
    END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strFromClause = @FromClause + @CRLF + '      INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON ' + dbo.udfGetOnJoinClause(@fkNameId);
        SET @nLevel = @Level + 1;
        EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
        SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
        FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
    --, @cColId, @pTableId, @pColId
    END
IF @Level = 0
    BEGIN
        SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
        SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
    END
CLOSE curs_children;
DEALLOCATE curs_children;

usage:

EXEC uspCascadeDelete
@ParentTableId = '[VAULT].[Package]',
@WhereClause = 'CreatedBy  =''VZBMNSTEST'''

Solution 5

For anyone coming here looking for a recursive delete that is involved with a hierarchical parent/child relationship in the same table, here's an example of how to do this using a CTE:

declare @headid int

;with RowsToDelete as (
    SELECT head.id
    FROM ..hierarchicaltable head
    WHERE id = @headid

    UNION ALL

    SELECT child.id
    FROM ..hierarchicaltable child
    INNER JOIN RowsToDelete parent
        ON parent.id = child.parentid
)
delete ht
FROM ..hierarchicaltable ht
INNER JOIN RowsToDelete d
    ON ht.id = d.id

View more solutions

Comments

[Captain Kenpachi]

Be careful of applying PL/SQL solutions to MSSQL/T-SQL. I don't see any problems with your code, but Oracle hates you and will do things differently just to be spiteful.

[Pricey]

Tested and works well in SQL Server. I was confused by the .. but just switch ..hierarchicaltable for [TableName]. Legendary!