Post date: Mar 20, 2012 12:38:17 PM
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 ONIF 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_idLEFT JOIN sys.foreign_key_columns AS fk ON fk.constraint_object_id=cstr.object_idLEFT JOIN sys.columns AS cfk ON fk.parent_column_id = cfk.column_id AND fk.parent_object_id = cfk.object_idLEFT JOIN sys.columns AS crk ON fk.referenced_column_id = crk.column_id AND fk.referenced_object_id = crk.object_idLEFT 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 orderEXEC(@sql)