Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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

, 8 Jan 2013
Rate this:
Please Sign up or sign in to vote.
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.

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  

License

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

Share

About the Author

Balaganesan Ravichandran
Software Developer
India India
Have 2+ Experience in various dot net technologies.
Eager to learn more...
My Technical blog : dotnet-assembly.blogspot.com

Comments and Discussions

 
GeneralMy vote of 1 PinmemberPraveenYadav8723-Sep-14 19:57 
QuestionHandle Tables at the same "Priorities" but one references the other so should be deleted First PinmemberTai Green5-Aug-14 1:11 
SuggestionA little modification - now handle multiple schemas PinmemberJohn B Oliver30-Jan-13 12:08 
GeneralRe: A little modification - now handle multiple schemas PinmemberBalaganesan Ravichandran31-Jan-13 1:42 
GeneralMy vote of 4 PinmemberJohn B Oliver30-Jan-13 10:12 
GeneralMy vote of 1 Pinmemberpraveen yadav6-Jan-13 22:38 
QuestionWhy should I use this dirty procedure ? Pinmemberpraveen yadav6-Jan-13 21:51 
Why should I use this dirty procedure ?
I can generate drop script using in-build script generator,It will take care of all the things(like dependencies).
Please try to post some use full post instead of posting some un-wanted foolish type of things.I have my food on plate and I can eat it directly(by spoon/hand) but u r saying to throw it in air and catch it like a dog !!! Wat an Idea Sir G !!!
AnswerRe: Why should I use this dirty procedure ? PinmemberBalaganesan Ravichandran6-Jan-13 22:52 
GeneralRe: Why should I use this dirty procedure ? Pinmemberpraveen yadav7-Jan-13 3:35 
GeneralRe: Why should I use this dirty procedure ? PinmemberJohn B Oliver30-Jan-13 10:19 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 8 Jan 2013
Article Copyright 2013 by Balaganesan Ravichandran
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid