Two new PowerShell functions for SQL Server

Posted on by admin
I just uploaded to new functions to the Microsoft Script Center:
The first will script out your table(s) structure, adn the second one will build your “insert into… Select…” script so you can populate the data of the empty table(s).  I included some documentation so is accessible using the get-help or help commands.
In the “Write-SQLCreateTable” function, I use some pre-defined scripting options but you may add/removed and only use the ones you need.  Here’s the ones I use:
[sourcecode language=”powershell”]
$scrp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Scripter $SQLInstanceName
$scrp.Options.DriAll = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.IncludeHeaders = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.AnsiPadding = $True
$scrp.Options.NoIdentities = $True
[/sourcecode]

For now, you need to provide the table schema, and the name of the tables will rename the same.  I will be updating this function to later allow to include a prefix to change the destination table name, and maybe include change the schema.

Keep in mind,  both functions will display the results on screen.  You can save the resutls to an PS Object and the use the “Invoke-SQLcmd” the  “-query” parameter to execute the scripts.

[string] $sqlquery = Write-SQLCreateTable …
Invoke-SQLCmd -ServerInstance MySQLServer -query $sqlquery

Sample pics

Function Write-SQLCreateTable
Function Write-SQLInsertSelect
Happy PowerShelling!!