Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell Script to Delete All Data in a SQL Server Database


By:   |   Updated: 2016-04-18   |   Comments (4)   |   Related: More > PowerShell

Problem

When working with SQL Server databases, sometimes there is a need to remove all data inside the database. Is there any way that I can do this efficiently especially when some of my tables can be very large?

Solution

The solution seems as simple as deleting data from tables (in a specified order if foreign key constraints exist), but there are a few challenges:

  • When you have big tables, using "delete table" can take a long time and generate huge transaction logs.
  • We can use "truncate table", but if a table is referenced, you cannot use truncate table even the referring table is empty
  • Also "Truncate table" cannot truncate a table that is schema bound by views or UDFs

You will see a few solutions mentioned in the Next Steps section, but for TRUNCATE table solutions, none is complete in considering that tables may be schema bounded by views and UDFs as mentioned in this article.

The solution can be a pure T-SQL solution, but it would be pretty lengthy with eye-sore dynamic code. As such, I will use PowerShell to address the above challenges, so here are the detailed steps (we assume the database is standalone, i.e. not involved in replication or mirroring):

  1. Script out all Foreign Keys and save the script in a string variable
  2. Drop the Foreign Keys
  3. If table is schema bound, then we script out the table into a variable and create a duplicate table (with different name) and use "alter table ... switch to ..." to move the original table's data to the newly created table, then we drop the newly created table.
  4. If the table is not schema bound, then we just truncate the table.
  5. Repeat from step 2 until all tables are processed
  6. Then recreate the Foreign Keys

For details about truncating tables and the challenges, please read Solution to T-SQL Table Truncation Challenge.

Prepare Test Environment

I used a backup of AdventureWorks 2012 and restored it to a new database named [AW2012].

Also as mentioned above, we assume [AW2012] is a single standalone database, with no replication, mirroring, or HA settings.

I only tested the code in SQL Server 2012, but I am sure it applies to SQL Server 2005+ versions.

Source Code

I assume you have installed SQL Server 2012 with the SQLPS module.

  • Start a PowerShell ISE window, then copy and paste the code into the window.
  • Change the first three variables, $mach, $sql_instance and $DBname to match your environment
  • Run the script
# This script is to truncate all tables in a database
# Author: Jeffrey Yao | 2016/03/15
# requires -version 3.0

import-module sqlps -DisableNameChecking;
set-location c:\;
# change the following three variables according to your environment needs
[string]$mach = $env:COMPUTERNAME;
[string]$sql_instance = 'default';
[string]$DBname = 'AW2012';

$svr = get-item "sqlserver:\sql\$mach\$sql_instance"

[String]$FT_index='';

[Microsoft.SqlServer.Management.Smo.Database]$db = get-item "sqlserver:\sql\$mach\$sql_instance\databases\$($DBname)"; # AW2012 

# This script is to truncate all tables in a database
# Author: Jeffrey Yao | 2016/03/15

import-module sqlps -DisableNameChecking;
set-location c:\;
# change the following three variables according to your environment needs
[string]$mach = $env:COMPUTERNAME;
[string]$sql_instance = 'default';
[string]$DBname = 'AW2012';

$svr = get-item "sqlserver:\sql\$mach\$sql_instance"

[String]$FT_index='';

[Microsoft.SqlServer.Management.Smo.Database]$db = get-item "sqlserver:\sql\$mach\$sql_instance\databases\$($DBname)"; # AW2012 
$db.tables.Refresh();

#script out FKs and save it to variable $fk_script
$db.tables | % -begin {[string]$fk_script=''} -process {$_.foreignkeys.refresh(); $_.foreignkeys | % {$fk_script +=$_.script() +";`r`n"} }

#drop foreign keys

$db.tables | % -begin {[email protected]()} -process { $fks += $_.ForeignKeys  };
foreach ($fk in $fks) {$fk.drop();}


$spt = new-object -TypeName "microsoft.sqlserver.management.smo.scripter" -ArgumentList $svr;

$spt.Options.WithDependencies = $true;

$so = new-object "microsoft.sqlserver.management.smo.scriptingoptions";
$so.DriPrimaryKey = $true;
$so.DriIndexes = $true;
$so.Indexes=$false;
$so.FullTextIndexes = $false;

foreach ($t in $db.tables )
{
    #we will check whether this table isSchemaBound by views/udfs
    #if yes, we will need to create a duplicate table with different table names, constraint names 
    #and then use "alter table ... switch .. " to do the trunation work
    #otherwise, we can simple do a truncate table

    $t.refresh();
    $dpt = $spt.DiscoverDependencies($t, $false);
    $collection=$spt.WalkDependencies($dpt);

    if ($collection.count -gt 1) 
    {
        if ($t.FullTextIndex -ne $null) # if there is FT index, we will drop it first
        {
            $t.FullTextIndex | % -Begin {[email protected]();} -process {$FT_index += $_.script(); $FTx +=$_;}
            foreach($f in $FTx) {$f.drop();}
            
        }

        [string]$ts=$t.script($so) -join "`r`n";
        $t.indexes | ? { ! ($_.isXmlIndex -or $_.isSpatialIndex -or ($_.IndexKeyType -eq 'DRIPrimaryKey'))} | % {$ts +=$_.script() + "`r`n"}

        $ts = $ts -replace "\sCONSTRAINT \[(\w+)]\s", ' CONSTRAINT [$1_x] '; # you must use the signle quote here
        
        $ts = $ts -replace "\sINDEX \[(\w+)]\s", ' INDEX [$1_x] ';
        $ts = $ts -replace "\[$($t.schema)]\.\[$($t.name)]", "[$($t.schema)].[$($t.name)_x]";
        try 
	    {   $db.ExecuteNonQuery($ts);
	        $qry = "alter table [$($t.schema)].[$($t.name)] switch to  [$($t.schema)].[$($t.name)_x];"
	        $qry;
	        
	        $db.ExecuteNonQuery($qry);
	        $qry = "drop table [$($t.schema)].[$($t.name)_x];"
	        $db.ExecuteNonQuery($qry);
	
	        #add back the FullText Index
	        if ($FT_index.Length -gt 0)
	        { $db.ExecuteNonQuery($FT_index); $FT_index = '';}
	    }
        catch
        {
            write-host "error running: $qry" -ForegroundColor Red;
        }
    } #$colletion.count -gt 1
    else
    { $t.TruncateData(); #direct truncate
      write-host "truncate table [$($t.Schema)].[$($t.name)]" -ForegroundColor yellow
    }
}

# resetup the FKs
$db.ExecuteNonQuery($fk_script);
$db = $null;
$svr = $null;

Before running the PowerShell script, we can run the following code before and after to get a picture of the number of rows in the table.

use AW2012
select top 10 [Table]=name, Rows
from sys.tables t
inner join sys.partitions p
on t.object_id = p.object_id
and p.index_id < 2
order by 2 desc

Here are the results. The left side is before the PowerShell script is run and the right side is after the PowerShell script is run. You can see that all of the tables now have 0 rows.

Before_After_Truncate_table_rows
Next Steps


Last Updated: 2016-04-18


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 19, 2016 - 12:23:28 PM - jeff_yao Back To Top

Thanks @mart for your comment.

My tips are always from my real world cases. :-)

Ok, here is the real story. I was engaged in reviewing a design of a database and especially the indexes (and some security model around their business rules). The database was developed and "maintained" by a bunch of different companies' consultants, you can call it "band-it on band-it", and they do not have the complete database creation script (esp. for indexes and permission granting scripts). There is no dedicated DBA resource there. 

So long story short, I want to get a copy of the database without any data in it, and thus I created this PS script and sent them to run against a restored database and then backup the empty database and send me the backup file.

Of course, they can script out the database, but I feel it is more convenient and complete to have a db backup for me to take a look.

I used this script in some other cases in which I want to empty a whole db except for some look-up tables. All I need to do is to put a filter against the table list so those lookup tables are not truncated. (of course, I did not put that logic in this tip just to make it simple, but it is easy for you to figure out).

I cannot help making a quick comment about using db snapshot. Snapshot is a nice feature yet not-so-commonly-used mainly because you may be surprised how large the snapshot file can grow when you have heavy load of data modifications.

 

I always believe comments is a good way for me to expand the explanation of my tips so thanks again for the comment, @mart.

 

Kind regards,

Jeff_yao

 

 


Tuesday, April 19, 2016 - 6:55:40 AM - mart Back To Top

Thanks for the post but one thing intersts me, if the database is one where the data can be removed, without issue, then it must surely be in a dev/test environment so why would you not just drop the database and recrate it from the original script? Alternatively, if it is actually a dev environment, then why not use a snapshot before filling it up then just restore from snapshot? 

I'm not knocking your method, and the use of Truncate instead of Delete is a fantastic point ot make, I'm just curious as to a real world use case?

Thanks in advance.

Mart


Monday, April 18, 2016 - 12:24:18 PM - jeff_yao Back To Top

 

Hi @Kevin Buchan, thanks for reading and commenting the tip. 

First, I believe that you were doing a delete instead of a truncate on all tables in the loop. The reason is that if you do a truncate on a table [X] that is referenced by another table [Y], you will get an error even if table [Y] is empty.  

Second, if you indeed use delete, yes, your way will work (been there don that myself). But the problem is, as I stated in this tip, deletion (not truncation) on large tables (with more than tens of millions rows) will be very slow and can generate huge amount of logs.

 

Regards,

Jeff_yao

 


Monday, April 18, 2016 - 8:16:11 AM - Kevin Buchan Back To Top

I'm no DBA, so maybe there will be an obvious logic bug to my suggestion...

What if, instead, I simply loop through all the tables that have rows and do a "truncate" until I get no errors?  Those with FK constraints will fail on one loop, but their children will get deleted.  Then, the next iteration, the "parent" table will get truncated.

I've done this in the past with success and it's stupid simple.

 


Learn more about SQL Server tools