THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Delete all data from a database

Sometimes I use this blog to share scripts that I know I'm going to need at some point in the future, this is one of those times.

The script below will remove all data from a database while respecting/preserving all foreign key constraints - very useful in a development or test environment if you want to get back to a "vanilla" snapshot of your database (i.e. one with no data in it). Usual caveats apply, this is not supported, use at your own risk blah blah blah.

In case you're interested, this is based on the script I provided a couple of years ago at Deriving a list of tables in dependency order which I also leveraged in sp_CascadingDataViewer!


/*Thus script clears all data from a database without requiring you to disable foreign keys
Basically, it orders all the tables according to FKs so that it deletes from them in the correct order.*/
SET NOCOUNT ON;
DECLARE    @schemaName SYSNAME;
DECLARE    @tableName  SYSNAME;
DECLARE    @level      INT;
DECLARE t_cur CURSOR FOR
WITH
fk_tables AS (
    
SELECT    s1.name AS from_schema    
    
,        o1.Name AS from_table    
    
,        s2.name AS to_schema    
    
,        o2.Name AS to_table    
    
FROM    sys.foreign_keys fk    
    
INNER    JOIN sys.objects o1                                 ON        fk.parent_object_id = o1.OBJECT_ID    
    
INNER    JOIN sys.schemas s1                                 ON        o1.schema_id = s1.schema_id    
    
INNER    JOIN sys.objects o2                                 ON        fk.referenced_object_id = o2.OBJECT_ID    
    
INNER    JOIN sys.schemas s2                                 ON        o2.schema_id = s2.schema_id    
    
/*For the purposes of finding dependency hierarchy we're not worried about self-referencing tables*/
    
WHERE    NOT    (    s1.name = s2.name                
            
AND        o1.name = o2.name)
)
,
ordered_tables AS
(        SELECT    s.name AS schemaName
        
,        t.name AS tableName
        
,        0 AS LEVEL    
        FROM    
(    SELECT    *                
                    
FROM    sys.tables                
                    
WHERE    name <> 'sysdiagrams') t    
        
INNER    JOIN sys.schemas s                                  ON        t.schema_id = s.schema_id    
        
LEFT    OUTER JOIN fk_tables fk                               ON        s.name = fk.from_schema    
                                                                      
AND   t.name = fk.from_table    
        
WHERE    fk.from_schema IS NULL
        
UNION    ALL
        
SELECT    fk.from_schema
        
,        fk.from_table
        
,        ot.LEVEL + 1    
        
FROM    fk_tables fk    
        
INNER    JOIN ordered_tables ot                              ON        fk.to_schema = ot.schemaName    
                                                                      
AND   fk.to_table = ot.tableName
)
SELECT    DISTINCT    ot.schemaName,ot.tableName,ot.LEVEL
FROM    
ordered_tables ot
INNER    JOIN (
        
SELECT    schemaName,tableName,MAX(LEVEL) maxLevel        
        
FROM    ordered_tables        
        
GROUP    BY schemaName,tableName
        
) mx
ON        ot.schemaName = mx.schemaName
AND        ot.tableName = mx.tableName
AND        mx.maxLevel = ot.LEVEL
ORDER BY LEVEL DESC
;
OPEN t_cur;
FETCH NEXT FROM t_cur INTO @schemaName,@tableName,@level;
WHILE @@FETCH_STATUS = 0
BEGIN
       DECLARE
@ParmDefinition NVARCHAR(500)   ;
      
DECLARE @vSQL           NVARCHAR(MAX)   ;
      
DECLARE @vRowCount      INT             ;
      
SET     @vSQL = 'DELETE FROM [' + @schemaName + '].[' + @tableName + '];';
      
SET     @vSQL = @vSQL + 'SET @vRowCount_OUT = @@ROWCOUNT;'
      
SET     @ParmDefinition = '@vRowCount_OUT INT OUTPUT';
      
EXEC    sp_executesql @vSQL,@ParmDefinition,@vRowCount_OUT=@vRowCount OUTPUT;
      
PRINT   CONVERT(NVARCHAR(100),@vRowCount) + ' rows deleted from [' +  @schemaName + '].[' + @tableName + ']';
      
FETCH NEXT FROM t_cur INTO @schemaName,@tableName,@level;
END

CLOSE
t_cur;
DEALLOCATE t_cur;
Published Monday, September 19, 2011 11:39 AM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Abhishek said:

Jamie,

you need to learn Powershell. this was so simple with PowerShell.

September 19, 2011 8:33 AM
 

jamiet said:

It wsa pretty simple with T-SQL too :)

What about Powershell makes it easier?

September 19, 2011 8:46 AM
 

Uri Dimant said:

Jamie

I think DELETE statement is more efficient if it has a WHERE condition otherwise I would prefer TRUNCATE..

September 20, 2011 1:07 AM
 

Uri Dimant said:

Jamie

I think DELETE statement is more efficient if it has a WHERE condition otherwise I would prefer TRUNCATE..

September 20, 2011 1:07 AM
 

Ken Trock said:

This is useful. It can otherwise be a pain to setup test data in various configurations due to FK constraints. In the past we've even gone so far as to not create some of our FKs until testing was finished. With a script like this we'll have more flexibility.

Thanks,

Ken

September 23, 2011 4:07 PM
 

Perry whittle said:

I prefer to script drop and create for FKs and constraints and then use truncate to clear the tables, faster clearance of data and less logging (only logs the de allocation of the extents). Also truncate will reset and column seed values too.

Incidentally, for the other comment above, you can't truncate a table that is referenced by a FK

September 24, 2011 4:57 AM
 

Naomi said:

September 24, 2011 9:18 PM
 

Nigel Meakins said:

Totally agree with Perry above. Delete rather than truncate is really not a goer for anything outside of Adventureworks.

September 26, 2011 7:30 AM
 

jamiet said:

Nigel,

I generally use this in development environments where data volumes are very low.

November 16, 2011 3:33 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement