TRUNCATE/DELETE/DROP tables referenced by a foreign key constraint

• Download Script.zip - 1.2 KB

Introduction  

This article explains how to delete/truncate/drop without affecting schema properties (disabling constraints), even though tables have relations.    

Why we need this 

We can also use the below in-built method for deleting all tables.

EXEC sp_MSforeachtable @command1 = 'DELETE ?' 

[But problem is: In TRUNCATE or DROP operations, SQL throws error if a table is referred in some other tables.]

If you want to drop or truncate a table that is referenced somewhere, you should get the following error.

“Could not drop object 'TableName' because it is referenced by a FOREIGN KEY constraint.” 

Using the code

CREATE PROCEDURE udpFlushAllTablesByDBName
(
    @DBName            VARCHAR(200),
    @FlushType        VARCHAR(20)
)
AS
BEGIN

    SET NOCOUNT ON;

    IF EXISTS (SELECT TOP 1 1 FROM SYS.TABLES WHERE OBJECT_ID = OBJECT_ID('tmpTable'))
    BEGIN
        DROP TABLE tmpTable
    END

    DECLARE @Query    NVARCHAR(MAX);
        
    SET @Query = 
    
    'WITH EliminateUnwanted (Name,[Object_ID],parent_object_id ,referenced_object_id)  AS  
    (
        SELECT
            [T].Name,
            [T].[Object_ID],
            [F].parent_object_id,
            [F].referenced_object_id
        FROM ' + @DBName + '.SYS.TABLES [T]
        LEFT JOIN ' + @DBName + '.SYS.FOREIGN_KEYS [F] ON [T].object_id = [F].parent_object_id   
        WHERE [T].Name NOT IN (''SysDiagrams'', ''tmpTable'') AND [T].TYPE = ''U''  
    ),  
    SetPriority (Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities)  AS  
    (
        SELECT
            *,
            OBJECT_NAME(referenced_object_id) AS parentObjectName,
            CASE
                WHEN referenced_object_id IS NULL AND [Object_ID] NOT IN ( 
                     SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted )  THEN 1
                WHEN [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') 
                      FROM EliminateUnwanted )  THEN 2
                WHEN ([Object_ID] IN ( SELECT ISNULL(referenced_object_id,'''') 
                      FROM EliminateUnwanted ) AND parent_object_id IS NULL)  THEN 4
                ELSE 3    
            END ''PRIORITY''
        FROM EliminateUnwanted
    ),
    DuplicateRemoval (Occurence, Name,[Object_ID],parent_object_id, 
           referenced_object_id, parentObjectName, Priorities)  AS  
    (
        SELECT  
            ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS Occurence
            ,* 
        FROM SetPriority 
    )
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Priorities) RowNo,
        Name,
        Object_ID,
        parent_object_id,
        referenced_object_id,
        parentObjectName,
        Priorities 
    INTO tmpTable
    FROM DuplicateRemoval 
    WHERE Occurence = 1'


    --SELECT @Query
    EXECUTE sp_executesql @Query

    DECLARE 
        @TableName        VARCHAR(100),
        @Count            BIGINT,
        @FlushMethod    VARCHAR(30);
    
    SELECT @FlushMethod =  CASE 
                                WHEN @FlushType = 'TRUNCATE' THEN 'TRUNCATE TABLE ' 
                                WHEN @FlushType = 'DROP' THEN 'DROP TABLE ' 
                                ELSE 'DELETE ' 
                            END;
    
    SET @Count = 1

    WHILE EXISTS(SELECT TOP 1 1 FROM tmpTable WHERE RowNo = @Count )
    BEGIN
        SELECT @TableName = NAME FROM tmpTable WHERE RowNo = @Count
        SET @Query = @FlushMethod + @DBName + '.dbo.' + @TableName
        
        EXECUTE sp_executesql @Query
        
        SET @Count = @Count + 1;
    END
    
    DROP TABLE tmpTable

    SET NOCOUNT OFF;

END

How it functions  

1. Get all tables from given database 
2. Ordered by priorities 



1. Independent tables have high priority. 
2. Least tables from relations (child tables) have medium priority 
3. Referenced tables (parent) have low priority 
3. Truncate/Delete/Drop tables by priorities