Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
3.33/5 (3 votes)
8 Jan 2013CPOL 23.9K   6   11
It works without affecting schema properties.

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.

SQL
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

SQL
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 
  3. Image 1

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Gislen Software Private Ltd
India India
Having 5+ years of experience in Microsoft technologies.
Eager to learn more...
My Technical blog : dotnet-assembly.blogspot.com

Comments and Discussions

 
QuestionGreat article. Pin
Jeevanandan J17-Mar-21 0:58
Jeevanandan J17-Mar-21 0:58 
GeneralMy vote of 1 Pin
PraveenYadav8723-Sep-14 19:57
PraveenYadav8723-Sep-14 19:57 
QuestionHandle Tables at the same "Priorities" but one references the other so should be deleted First Pin
Tai Green5-Aug-14 1:11
Tai Green5-Aug-14 1:11 
SuggestionA little modification - now handle multiple schemas Pin
John B Oliver30-Jan-13 12:08
John B Oliver30-Jan-13 12:08 
GeneralRe: A little modification - now handle multiple schemas Pin
Balaganesan Ravichandran31-Jan-13 1:42
Balaganesan Ravichandran31-Jan-13 1:42 
GeneralMy vote of 4 Pin
John B Oliver30-Jan-13 10:12
John B Oliver30-Jan-13 10:12 
GeneralMy vote of 1 Pin
PraveenYadav876-Jan-13 22:38
PraveenYadav876-Jan-13 22:38 
QuestionWhy should I use this dirty procedure ? Pin
PraveenYadav876-Jan-13 21:51
PraveenYadav876-Jan-13 21:51 
AnswerRe: Why should I use this dirty procedure ? Pin
Balaganesan Ravichandran6-Jan-13 22:52
Balaganesan Ravichandran6-Jan-13 22:52 
Ya, it’s really useful if you want to do this through pro-grammatically.

Wise men always try to automate repeated works. Hope you understand. Smile | :)
GeneralRe: Why should I use this dirty procedure ? Pin
PraveenYadav877-Jan-13 3:35
PraveenYadav877-Jan-13 3:35 
GeneralRe: Why should I use this dirty procedure ? Pin
John B Oliver30-Jan-13 10:19
John B Oliver30-Jan-13 10:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.