SQL Server: DROP All But A Few Tables With PowerShell (or DROP ALL)

Recently, I received a request to backup a dozen tables or so tables out of 12 thousand tables. I had to retain all the indexes, statistics etc. The goal was to hand this over to the vendor for analysis as a database backup.

I could have copied the selected tables over to a new database using the PowerShell function I had published earlier and backed that up but since the tables to backup were quite large, I skipped that route

Alternate way to keep 12 big tables out of 12 thousand:

The alternate way I decided to take was to

  • Backup and restore to a “Work” database
  • Drop foreign key constraints
  • Drop all the tables except the ones I need to keep
  • Shrink the database (Yes, I know this is very bad!)
  • Recreate the foreign key constraints (ignore errors for missing tables)
  • Rebuild indexes if necessary
  • Update statistics
  • Backup and send to vendor

When you have Foreign Key constraints, you cannot drop the referenced Primary Key table and you will receive an error such as:

Drop failed for Table ‘dbo.JanaTest1’. (Microsoft.SqlServer.Smo)

Could not drop object ‘dbo.JanaTest1’ because it is referenced by a FOREIGN KEY constraint. (Microsoft SQL Server, Error: 3726)

In my case, I did not have any foreign key constraints but if you do, you may have to script them out, drop and then recreate after you are done dropping the unwanted tables. Ignore errors when you recreate constraints on tables that don’t exist anymore. The Shrink was necessary in my case since I needed to get the size of the database down from about 1.5 TB to the 40 GB required for the dozen tables after all the unwanted tables were removed.

PowerShell function to Drop all tables EXCEPT given input tables:

The PowerShell function below (the crux of this post), essentially drops all the tables except the ones given in the input parameter.

WordPress messes up PowerShell block comments. So, I have separated the block comment that goes at the beginning of the code

The Comments

#
#######################
#
.SYNOPSIS
    Removes all tables from a given Instance/Database except the given tables

.DESCRIPTION
    Generally, it is easier to drop all unnecessary tables and make a backup than
        to backup just a handful of big tables to a separate database.
    Using this function, you can drop all tables except the given tables

.INPUTS
    Instance, Database and Table drop exclusions

.OUTPUTS
    None

.EXAMPLE 

        [string] $instanceName = 'MYSQLHOST\MYINSTANCE'
        [string] $databaseName = 'MyDB_ForVendor'
        [string[]] $dropTablesExcept = @('[dbo].[TABLE_Classification]',
                                        '[dbo].[TABLE_CustAcct]',
                                        '[dbo].[TABLE_FxRates]',
                                        '[dbo].[TABLE_Instrument]',
                                        '[dbo].[TABLE_Instrument_Rating]',
                                        '[dbo].[TABLE_Issuer]',
                                        '[dbo].[TABLE_Issuer_Rating]',
                                        '[dbo].[TABLE_LookThrough]',
                                        '[dbo].[TABLE_Portfolio]',
                                        '[dbo].[TABLE_REP_GRID]',
                                        '[dbo].[TABLE_Report_Parameters]',
                                        '[dbo].[TABLE_SAP_Accounting]',
                                        '[dbo].[TABLE_Segmentation]',
                                        '[dbo].[TABLE_Valuation]')

        Remove-SQLTableExcept `
            -InstanceName $instanceName `
            -DatabaseName $databaseName `
            -DropTablesExcept $dropTablesExcept `
            -Verbose

.EXAMPLE 

        [string] $instanceName = 'MySourceServer\SourceInst'
        [string] $databaseName = 'MySourceDB'
        [string[]] $dropTablesExcept = @('dbo.TABLE_DWH_SEC_INDUSTRY_RPT_DQM',
                    'dbo.TABLE_MAS_SEC_INDUSTRY',
                    'dbo.TABLE_DWH_SEC_CALC_RPT_DQM',
                    'dbo.TABLE_MAS_SEC_CALC',
                    'dbo.TABLE_MAS_CLASS_DESC',
                    'dbo.TABLE_MAS_CLASS_MAP',
                    'dbo.TABLE_MAS_CLASSIFICATION_CATEGORY',
                    'dbo.TABLE_MAS_SAP_ACC_ENTRY',
                    'dbo.TABLE_DWH_SECURITIES_RPT_DQM',
                    'dbo.TABLE_MAS_SEC_CLASS',
                    'dbo.TABLE_MAS_SECURITIES',
                    'dbo.TABLE_MAS_COUNTRY',
                    'dbo.TABLE_MAS_CURRENCY',
                    'dbo.TABLE_BPS_SEC',
                    'dbo.TABLE_BPS_ULT',
                    'dbo.TABLE_DQM_SEC_CALC_CONTROL_RPT',
                    'dbo.TABLE_DQM_SEC_INDUSTRY_CONTROL_RPT',
                    'dbo.TABLE_DQM_SECURITY_CONTROL_RPT',
                    'dbo.TABLE_MAS_ISSUER')

        Remove-SQLTableExcept `
            -InstanceName $instanceName `
            -DatabaseName $databaseName `
            -DropTablesExcept $dropTablesExcept `
            -Verbose
.NOTES 

Version History
    v1.0  - Mar 13, 2019. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

.LINK
    sqljana.wordpress.com
#
#

The Code

I am using SQLPS module but you can replace it with SQLSERVER module without having to change logic.

function Remove-SQLTableExcept
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $InstanceName,

        [Parameter(Mandatory=$true)]
        [string] $DatabaseName,        

        [Parameter(Mandatory=$true)]
        [string[]] $DropTablesExcept

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"  

    [string] $connString = "Data Source=$InstanceName;Initial Catalog=$DatabaseName;Integrated Security=True;"
    [int] $counter = 0

    try
    {    

        $stepName = "[$fn]: Import SQLPS module and initialize source connection"
        #---------------------------------------------------------------
        Write-Verbose $stepName

        Import-Module 'SQLPS'
        $sourceServer = New-Object Microsoft.SqlServer.Management.Smo.Server $InstanceName
        $sourceDatabase = $sourceServer.Databases[$DatabaseName]
        $sourceTables = $sourceDatabase.Tables

        foreach($table in $sourceTables)
        {
            $tableName = $table.Name
            $schemaName = $table.Schema
            $tableAndSchema = "[$schemaName].[$tableName]"

            #We will check for all these combinations against the input provided
            $tableSchemaBothNoBrkt = "$schemaName.$tableName"
            $tableNoBrktAndSchema = "[$schemaName].$tableName"
            $tableAndSchemaNoBrkt = "$schemaName.[$tableName]"

            $counter = $counter + 1
            Write-Progress -Activity "Progress:" `
                        -PercentComplete ([int](100 * $counter / $sourceTables.Count)) `
                        -CurrentOperation ("Completed {0}% of the tables" -f ([int](100 * $counter / $sourceTables.Count))) `
                        -Status ("Working on table: [{0}]" -f $tableAndSchema) `
                        -Id 1

            if ($DropTablesExcept.Contains($tableAndSchema) `
                    -or $DropTablesExcept.Contains($tableSchemaBothNoBrkt) `
                    -or $DropTablesExcept.Contains($tableNoBrktAndSchema) `
                    -or $DropTablesExcept.Contains($tableAndSchemaNoBrkt))
            {
                Write-Verbose "[$fn]: Skipping table $tableAndSchema"
            }
            else
            {        

                Write-Verbose "[$fn]: ---------------------------------------------------------------"
                $stepName = "[$fn]: About to drop table [$tableAndSchema]"
                Write-Verbose $stepName
                Write-Verbose "[$fn]: ---------------------------------------------------------------"

                Write-Verbose "[$fn]: Drop table [$tableName] in target if it exists"
                #---------------------------------------------------------------
                Write-Verbose $stepName

                $tableDropScript = "IF EXISTS (SELECT 1 WHERE OBJECT_ID('$tableAndSchema') IS NOT NULL)
                                    BEGIN
                                        PRINT ('About to drop table $tableAndSchema')
                                        EXEC('DROP TABLE $tableAndSchema')
                                    END"

                Invoke-Sqlcmd `
                            -ServerInstance $InstanceName `
                            -Database $DatabaseName `
                            -Query $tableDropScript

            }
        }

        Write-Verbose 'Cleanup'
        #---------------------------------------------------------------
        $sourceServer = $null                

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to copy table(s). Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Here is a code sample to remove all the tables EXCEPT the ones specified:

[string] $instanceName = 'MYSQLHOST\MYINSTANCE'
[string] $databaseName = 'MyDB_ForVendor'
[string[]] $dropTablesExcept = @('[dbo].[T_RMT_Classification]',
                                        '[dbo].[TABLE_CustAcct]',
                                        '[dbo].[TABLE_FxRates]',
                                        '[dbo].[TABLE_Instrument]',
                                        '[dbo].[TABLE_Instrument_Rating]',
                                        '[dbo].[TABLE_Issuer]',
                                        '[dbo].[TABLE_Issuer_Rating]',
                                        '[dbo].[TABLE_LookThrough]',
                                        '[dbo].[TABLE_Portfolio]',
                                        '[dbo].[TABLE_REP_GRID]',
                                        '[dbo].[TABLE_Report_Parameters]',
                                        '[dbo].[TABLE_SAP_Accounting]',
                                        '[dbo].[TABLE_Segmentation]',
                                        '[dbo].[TABLE_Valuation]')

Remove-SQLTableExcept `
            -InstanceName $instanceName `
            -DatabaseName $databaseName `
            -DropTablesExcept $dropTablesExcept `
            -Verbose

Table names and Schema name prefix/suffixes are flexible

The table and schema names may have square brackets around them or not. The logic checks the different combinations to make sure.

In other words, all of the following are acceptable and mean the same:

  • [dbo].[TABLE_Valuation]
  • [dbo].TABLE_Valuation
  • dbo.[TABLE_Valuation]
  • dbo.TABLE_Valuation

You do have to pass in both the schema and the table name as there can be two tables with the same name across schemas.

As a nicety, it even shows progress as it works through all the tables so you get a sense for how long it will take!

DROPping ALL tables in a database

Just pass in an empty array for the DropTablesExcept  parameter. For example

[string] $instanceName = 'MYSQLHOST\MYINSTANCE'
[string] $databaseName = 'MyDB_ForVendor'
[string[]] $dropTablesExcept = @('')

Remove-SQLTableExcept `
            -InstanceName $instanceName `
            -DatabaseName $databaseName `
            -DropTablesExcept $dropTablesExcept `
            -Verbose

Conclusion

So, if you ever have a need like what I had, feel free to use the above code but first make a backup to save yourself from a mistake as this function DROP’s tables!

One thought on “SQL Server: DROP All But A Few Tables With PowerShell (or DROP ALL)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s