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!
SQL Jana 
One thought on “SQL Server: DROP All But A Few Tables With PowerShell (or DROP ALL)”