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.

VS Code – Code Runner Extensions

 

Hum! I just found out that in SQL Server 2014 (SP2 installed), while migrating from SQL Server 2005, one of my PowerShell script (I’ve been using for a long time) that uses SMO to truncate tables. But, when running it against a SQL Server 2014 database, I’m getting an error:

“..this property is not available on SQL Server 2014.”

For mi surprise, I ran the same PowerShell script against SQL Server 2016 and it works fine.

Here’s a sample function that does a Truncate to all my tables in a database:

[sourcecode language=”powershell”]
function Clear-DatabaseTable
{
[CmdletBinding()]
param (
[string]$SQLServerInstanceName,
[string]$SQLServerDatabasename
)

[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
$SQLSrvObj = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$SQLServerDatabaseName].tables;

## Database tables rowcounts before truncate:
$tables | Select @{ Label = “TruncatedTable”; Expression = { $_.Schema + “.” + $_.Name }; }, `
@{ Label = “PrevRowCount”; Expression = { $_.rowcount }; } | FT -auto

$ReadThis = `
“**********************************************************************`r`n ” `
+ “Do you really want to Truncate all tables in $SourcedbName ? Y/N `r`n” `
+ “**********************************************************************`r`n “;
$Cont = Read-Host $ReadThis;
[console]::ResetColor();

if ($Cont.ToUpper() -eq ‘Y’)
{
foreach ($t in $tables)
{
Write-Verbose “Truncating Table $($t)” -Verbose;
$t.TruncateData();
};
};
};

[/sourcecode]

Load this function into you session and then run the following command:

[sourcecode language=”powershell”]
Clear-DatabaseTable -SQLServerInstanceName ‘MTRINIDADLT2\MSSQL2K16A’ `
-SQLServerDatabasename ‘devMaxTest’;

[/sourcecode]

The results against SQL Server 2016 were successful:

But, when running against SQL Server 2014, I get the error:

I logged this issue under SQL Server UserVoice: https://manage.uservoice.com/forums/108777-database-manager-for-sql-azure-feature-suggestions/suggestions/16286755-sql-server-2014-is-missing-smo-truncatedata-met

Please Vote on it!