Last updated on November 9, 2017
@brianszadek says: I am looking to automate the scripting of objects with PowerShell for easier source control integration.
I thought this would be a little bit easier, but it looks like the SMO way of doing this is still fairly complicated. Maybe something I can look at simplifying for the project in a bit ๐
For completeness I also included my favorite tool (schemazen) and Microsoft’s new scripting tool if you want a quick shell approach.
PowerShell: (dbatools)
$SqlInstance = "192.168.0.23,1433" $DatabaseName = "breakme" $Server = Connect-DbaInstance $SqlInstance $Database = $Server.Databases[$DatabaseName] # Define an empty list $ObjectList = @() $Options = New-DbaScriptingOption $Options.AllowSystemObjects = $Options.AppendToFile = $false $Options.Triggers = $Options.ToFileOnly = $Options.ScriptBatchTerminator = $Options.DriAll = $Options.Indexes = $Options.Default = $Options.SchemaQualify = $Options.ClusteredIndexes = $Options.NonClusteredIndexes = $true # Surprisingly, $Database.EnumObjects will return a set of strings, not SMO objects, this is kind of sucky. $ObjectList += foreach ($ob in $Database.EnumObjects()){ # Get all objects if ($ob.Schema -notin "sys","INFORMATION_SCHEMA"){ # Not in system schemas $o = $Database.Resolve($ob.Urn) # Resolve the object path to an SMO object if (($o | Get-Member | Select-Object -exp Name) -contains "Script"){ # If it has a script property $o # Return it } } } function Rename-ObjectToSqlFile { param ($ObjectName) $Name = $ObjectName -match '.*\/(.*)' if ($Name -eq $false){ $Name = $ObjectName } else { $Name = $matches[1] } $Name = $Name + ".sql" return $Name } # Iterate, get one file per object, decide on how you want to name them, because things like event notifications are uris foreach ($obj in $ObjectList) { $Name = Rename-ObjectToSqlFile -ObjectName $obj.name Export-DbaScript $obj -ScriptingOptionsObject $Options -Path $Name # Export it with the dbatools powers } # Or export based on category Export-DbaScript $ObjectList -ScriptingOptionsObject $Options # Export object categories to each file
Note: Export-DbaScript currently has a bug that prevents servers using SQL Authentication from scripting, this has been fixed in the next version.
Command line executables
Schema Zenย (C#) (Free, OSS)
If you haven’t heard of SchemaZen, its a great and insanely fast tool for exporting all the objects in a database to a folder, and if needed, take the same folder and create a database from it.
There are also some experimental data export features.
MSSQL Scripter (python) (Free, OSS)
If you haven’t used the MSSSQL Scripter, its Microsoft’s tool that utilizes the newย SqlToolsService to request and script out DDL information. They have a lovely architecture page if you want to learn more.
You should add a parameter for the root folder to export the scripts to.
That’s a great idea!
I was also just thinking on this a minute ago, and I should probably just make a subfolder for each script type (like schemazen already does.)
If I remember I will take a crack at it later today ๐
Hi,
did you ever create a script that does different object types in sub folders?
thanks
rich
Hey Rich – I hadn’t – but the simplest way would probably be by inspecting the $obj.urn.type and adding that to the -Path parameter, it will list out things like ServiceRoute, SqlAssembly, StoredProcedure, etc.