PowerShell: Drop All SQL Server Tables, Views, SP’s, Functions, Types etc.

I hope this script does not become infamous for the wrong reasons! Please use caution.

I had to help a team recreate everything in a database and test their scripts but leave the roles and role grants in place. Basically, this meant that I could have scripted out the permissions and recreated the database but I thought it would be easier and more re-runnable to just drop everything else except the permissions.

Prerequisites:

You need the all-encompassing dbatools PowerShell Module for SQL Server. If you don’t have it already, simply install it by running:

Install-Module dbatools;

What exactly does the script drop?

The script you see below, will remove EVERY object of ALL of the below types given a SQL Instance and database name

• Tables
• Views
• Stored procedures
• User defined functions
• Table Types
• Types
• Sequences

It should be easy enough to add the missing things that need to be dropped like Database level triggers, XML schema collections, Assemblies etc. I simply did not add it because the above are the most common object types people would want to drop and recreate. You can also add conditions around specific schemas to drop.

* * * W A R N I N G * * * :

It PAUSE’s when you run it, thereby giving you a chance to press CTRL+C and stop the script if you see the wrong instance or database name. If you don’t have a backup, you will lose everything and there is no way to get the dropped objects back! You have been warned.

#Version History
#    v1.0  - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com] - Initial Release - Feb.03.2020
#
#Usage 1:
# Simply run the script and you will be prompted for the Instance and Database to clear
#
#Usage 2 (from within PowerShell):
# . c:\path\to\this\script\Clear-SQLDatabase.ps1 -SqlInstance 'MyServer\MyInstance' -Database 'MyDatabase'
#
#Usage 3 (from command prompt):
# powershell.exe c:\path\to\this\script\Clear-SQLDatabase.ps1 -SqlInstance 'MyServer\MyInstance' -Database 'MyDatabase'
#

param(
        [Parameter(Mandatory=$true)]
        [System.String]
        $SqlInstance,

        [Parameter(Mandatory=$true)]
        [System.String]
        $Database
    )

Pause "This script will remove all objects from database [$Database] on SQL Server Instance: [$SqlInstance]. **** Press CTRL+c to sop now ****";

#---------------------
#Get the SMO object tree
#---------------------
$DatabaseSMO = Get-DbaDatabase -SqlInstance $SqlInstance -Database $Database;

#Drop all the tables. Loop is to keep retrying on tables that could not be dropped due to FK's.
while(1 -eq 1)
{
    $tablesArray = $DatabaseSMO.Tables | Select-Object Schema,Name;

    if ($tablesArray.Count -eq 0)
    {
        break;
    }

    foreach($table in $tablesArray)
    {
        Write-Host("About to drop table $($table.Schema).$($table.name)");
        Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP TABLE [$($table.Schema)].[$($table.name)]";
    }

    $DatabaseSMO.Tables.Refresh();
}

#---------------------
#Drop all the views
#---------------------
$viewsArray = $DatabaseSMO.Views |
                Select-Object Schema,Name |
                Where-Object {($_.Schema -ne 'sys') -and ($_.Schema -ne 'INFORMATION_SCHEMA')};
foreach($view in $viewsArray)
{
    Write-Host("About to drop view $($view.Schema).$($view.name)");
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP VIEW [$($view.Schema)].[$($view.name)]";
}

#---------------------
#Drop all the stored procedures
#---------------------

$spsArray = $DatabaseSMO.StoredProcedures |
                Select-Object Schema,Name |
                Where-Object {($_.Schema -ne 'sys')};
foreach($sp in $spsArray)
{
    Write-Host("About to drop sp $($sp.Schema).$($sp.name)");
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP PROCEDURE [$($sp.Schema)].[$($sp.name)]";
}

#---------------------
#Drop all the functions
#---------------------

$fnsArray = $DatabaseSMO.UserDefinedFunctions | Select-Object Schema,Name | Where-Object {($_.Schema -ne 'sys')};
foreach($fn in $fnsArray)
{
    Write-Host("About to drop function $($fn.Schema).$($fn.name)");
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP FUNCTION [$($fn.Schema)].[$($fn.name)]";
}

#---------------------
#Drop all the table types
#---------------------

$tableTypesArray = $DatabaseSMO.UserDefinedTableTypes |
                Select-Object Schema,Name |
                Where-Object {($_.Schema -ne 'sys')};
foreach($tt in $tableTypesArray)
{
    Write-Host("About to drop table type $($tt.Schema).$($tt.name)");
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP TYPE [$($tt.Schema)].[$($tt.name)]";
}

#---------------------
#Drop all the user-defined types
#---------------------

$typesArray = $DatabaseSMO.UserDefinedTypes |
                Select-Object Schema,Name |
                Where-Object {($_.Schema -ne 'sys')};
foreach($type in $typesArray)
{
    Write-Host("About to drop type $($type.Schema).$($type.name)");
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP TYPE [$($type.Schema)].[$($type.name)]";
}

#---------------------
#Drop all the sequences
#---------------------

$seqArray = $DatabaseSMO.Sequences |
                Select-Object Schema,Name |
                Where-Object {($_.Schema -ne 'sys')};
foreach($seq in $seqArray)
{
    Write-Host("About to drop type $($seq.Schema).$($seq.name)");
    Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "DROP SEQUENCE [$($seq.Schema)].[$($seq.name)]";
}

That’s it! When you finish running, all the objects would be gone. If you plan to re-install objects using a set of scripts, you can call it like this:

Invoke-DbaQuery -SqlInstance 'myserver\myinstance' -Database 'mydatabase' -File "c:\PathToScript\01_AllScripts.sql" -Verbose
Invoke-DbaQuery -SqlInstance 'myserver\myinstance' -Database 'mydatabase' -File "c:\PathToScript\10_AllViews.sql" -Verbose
Invoke-DbaQuery -SqlInstance 'myserver\myinstance' -Database 'mydatabase' -File "c:\PathToScript\20_AllProcs.sql" -Verbose

Conclusion:

If you ran the script and lost tables/views or anything else that you want to get back, my answer again is “restore it from your last backup”!

Advertisement