Just Code‎ > ‎

TSQL - Delete ALL data from ALL tables, with respect to foreign keys

posted Mar 20, 2012, 5:38 AM by Peter Henell   [ updated Mar 20, 2012, 5:41 AM ]
This script will delete the data from all tables even if there are foreign keys between the tables.

It will first collect information about the hierarchy so that it knows which tables have foreign key to another table. This is the "base" CTE
It will then recursively find the tables that have no foreign keys (iteration one), and then tables that have foreign key to those tables (iteration two have foreign key to only iteration one), and so on recursively. This is the "more" CTE

After this it will have a set of tables (table names and their schema) with information about what level they are. The level indicate the hierarchy, level zero does not have foreign key to anything, level one only have foreign keys to the level zero and so on.

If we sort this set based on the level and create some dynamic sql to delete the data from the tables, then we are done. 
We are now deleting all the data from all the tables, in an order so that foreign key constraints are respected and violations are avoided.

Do NOT use this in production environments. This snippet is meant to be used as a tool in development and test environments where the data is not important.

Note, this could fail if a table have a foreign key constraint to itself. 
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp;

WITH base AS (

    
SELECT 
    tbl.name AS [Table_Name]
    ,SCHEMA_NAME(tbl.schema_id) AS Table_Schema
    ,tbl2.name AS [ReferencedTable]
FROM 
    sys.tables AS tbl
LEFT JOIN 
    sys.foreign_keys AS cstr
    ON cstr.parent_object_id=tbl.object_id
LEFT JOIN 
    sys.foreign_key_columns AS fk
    ON fk.constraint_object_id=cstr.object_id
LEFT JOIN 
    sys.columns AS cfk
    ON fk.parent_column_id = cfk.column_id
    AND fk.parent_object_id = cfk.object_id
LEFT JOIN 
    sys.columns AS crk
    ON fk.referenced_column_id = crk.column_id
    AND fk.referenced_object_id = crk.object_id

LEFT JOIN 
    sys.tables AS tbl2
    ON fk.referenced_object_id = tbl2.object_id
    )
,
more AS
(
    SELECT 
        Table_name
        , Table_Schema
        , [ReferencedTable]
        , 0 AS [level]
        
    FROM 
        base
    WHERE 
        [ReferencedTable] IS NULL
    
    UNION ALL
    
    SELECT 
          b.Table_name
        , b.Table_Schema
        , b.[ReferencedTable]
        , m.[Level] + 1 AS [Level]
        
    FROM 
        base b
    INNER JOIN 
        more m
        ON    b.[ReferencedTable] = m.Table_Name
)
        
        SELECT DISTINCT
              Table_Name
            , Table_Schema
            , [level]
            
        INTO #tmp
        FROM 
            more m
        WHERE NOT EXISTS(SELECT 1 FROM more m2 WHERE m2.level > m.level AND m2.table_name = m.table_name)
                
        ORDER BY 
            m.[level]
            

-- Generate the SQL query to delete all the data
-- The deletion will be done in order so that no referential contraints will be a problem.            
DECLARE @sql VARCHAR(MAX) = ''
SELECT @sql = @sql + 'delete ' + Table_Schema + '.' + table_name + '; ' FROM #tmp ORDER BY level DESC

-- Execute deletion from all tables in the correct order
EXEC(@sql)


Comments