Here’s a good example of how useful is SMO in your SQL Server Developement environment. As I the task repopulating data in a quarterly basis, I need to clear all my staging tables in databases before my import processes. OK! I could just create a T-SQL query with all my truncate lines. But, this will force me to open SSMS, file open my T-SQL file, and then execute the query. Or, I can create simple PowerShell function so I can run from my PowerShell Console, or ISE editor when it’s needed.
In my previous blog, I showed the Get-TSQLTableRecordCount function which allow me to either list all my databases, or display a list of row count for all the tables in the database. I can use this function to verify that my PowerShell table truncate function is working.
So, when using SMO, how many lines of code it will take me to create this function? About 11 line of code. But truely, the heart of this code is only 3 lines, and rest if only adding the necessary code to it a function.
Here’s the basic 3 lines that make the truncate work:
[sourcecode language=”powershell”]
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
[/sourcecode]
Of course, you need to provide both the SQL Server instance, and the database name
Note: Be careful with this function because is meant to truncate all tables in the given Database.
I went back to SSMS to graphically confirm that all data was removed, but I found out that the database size was still showing. I knew I had to incorporate the SMO Shrink() method for the database after doing the SMO TruncateData() method on each of the tables.
Here’s an image showing the database size after the truncating all tables:
So, I added the following line after the truncate tables:
[sourcecode language=”powershell”]
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
[/sourcecode]
Image after truncate and running the SMO Shrink() method:
Putting everything together into a basic function performing your tables truncate and database shrink process:
[sourcecode language=”powershell”]
Function Clear-TSQLTruncateAllTables{
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True, Position=0)] [ValidateScript({$_ -ne $null})] [string] $SQLInstanceName,
[Parameter(Mandatory=$True, Position=1)] [ValidateScript({$_ -ne $null})] [string] $DatabaseName
)
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
}
[/sourcecode]
This is just a teaser code so you can see the potential and benefit in understanding SMO in PowerShell. I will be incorporating a more elaborate version of this function into my SQLDevTools Module available in CodePlex in the next release.
For more information on this SMO DatabaseShrink Method, here’s some links: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.shrink(v=SQL.110).aspx and http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.shrinkmethod(v=SQL.110).aspx