PowerShell – SQL Server SMO Drop table in Database

In one of my previous blog, I mention about issue I found with SQL Server 2014 SMO not supporting the *.TruncateData() method. So, while working on one of my company SQL Server migration I need my script to cleanup some tables and I ended up doing a quick script to drop a selected list of tables.

Before we have the script, we need to create the list of tables. Basically, I had previously create two PowerShell objects and use the compare-object cmdlet to identify only the same tables found in both objects.

Then I can proceed to drop tables from the list object labeled $ToDelete:

[sourcecode language=”powershell”]
## – Create compare results of list of tables:
$list = compare-object -ReferenceObject $newSAP -DifferenceObject $oldSAP -IncludeEqual;

## – Save list for future reference:
$list | Where{ $_.sideindicator -eq ‘==’ } | out-file c:\temp\SAP_tablesToDelete.csv;
$list | Where{ $_.sideindicator -eq ‘=>’ } | out-file c:\temp\SAP_tablesMissing.csv;

## – Only get the list of tables to be deleted:
$toDelete = $list | Where{ $_.sideindicator -eq ‘==’ } | Select-Object InputObject;

## – Using SMO steps to work with tables:
$SQLServerInstanceName = ‘TSQLDSP01’; $SQLServerDatabasename = ‘dgSAP’;

## – Connect to SQLServer instance and get table name list:
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
$SQLSrvObj = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$SQLServerDatabaseName].tables;

## – Step to go through tables list and the drop tables:
$global:cnt = 1; [array]$results = $null;
$results = foreach ($t in $tables)
## – Loop through tables to be drop:
foreach ($i in $toDelete)
## – Chekc each table $t in the $i selected list:
if ($t.name -eq $i.InputObject)
## – Drop table process:
## -> $t.Drop();
$r = “[$($global:cnt.ToString(‘000’))] – Sql $($t.name) = Object $($i.InputObject) removed”
Write-Verbose $r -Verbose;
$r; $global:cnt++

## – Refresh tables object without dropped tablename:

This *script will assist in the third-party application to recreate the tables meeting for our migration.

*Note: Just in case, I commented out the line having the $t.Drop() method.