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:
$tables.Refresh();
};
[/sourcecode]
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.