Like so many of you out there in Internetland, I’m fairly new to Powershell, which is fairly new to the world. Recently I wanted a simple way to script out database objects, and a couple of you were kind enough to share your solutions with me.
Sean Likes Powershell
I also worked with Sean, who likes very much to talk about Powershell (I recommend you start with What Makes Powershell Awesome?, my personal favorite blog on PS). Here is the current list of his Powershell videos:
- PowerShell Basics
- Scripting DB Objects in PowerShell
- Scripting DB Objects in Powershell (Advanced Topics)
- Working with PSDrive in PowerShell
- Powershell with –NoExit
- Change SQL Server Job Owner
- Grant DB Permissions
- Grant Schema Permissions in Powershell
- Building a Powershell BCP Routine
- Powershell Profiles
- Beginning Powershell for DBAs 1
- Beginning Powershell for DBAs 2
- Beginning Powershell for DBAs 3
- Beginning Powershell for DBAs 4
- Replace Text in File
- Find DB Files with Powershell
- Pass Params to Scripts (Powershell)
- Index Scripting Problems
- Change DB Compatibility Level in Powershell
- Get a Specific Method in Powershell
- Add User to Windows Group with Powershell
- Build .Net Objects in Powershell
- Start Agent Jobs in Powershell
- Start Agent Jobs in Powershell: Part 2
- Manage DB Files in Powershell
Yes, I am bragging on him. You should listen to your friend Sean, he’s a cool dude.
I Like Powershell
Anyway, the Scripting DB Objects in PowerShell video was almost what I needed, but not quite. Sean goes over how to script out objects to a single file; I’m scripting objects to check into source control, so I want one object per file. Last night we finally got around to sitting down so he could show me the code.
dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}
That’s it. That’s everything you need to script out every single table in a database to an individual file. I <3 Sean, and I <3 Powershell. At the risk of stealing his thunder, let me `splain a little.
We All Like Powershell!
There are SMO solutions, but there’s a definite efficiency, elegance, and convenience to having everything you need in a single line of code.
dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}
Start SQLPS from within SSMS 2008 (just right-click your database of choice in the Object Explorer, and “Start Powershell”). The SQL Server Powershell will open, with the path to your chosen database already loaded. Type cd Tables and hit Enter to navigate to the Tables “folder”. Now, to break down the Powershell command:
- dir
dir gets a listing of all the tables in that database - dir |
the pipe (|) pipes that list to the next part of the command - dir | %{}
%{} is the shorthand version of a FOR loop in PS*. So this FOR loop is FOR each table piped from dir - dir | %{$Table = $_.Name}
$Table is a variable. We set it = the current table name passed in to the loop, which is signified by $_.Name. Note that $_ always means “the current one”, in this case “the current table”. - dir | %{$Table = $_.Name; $_.script()}
Within the loop, after we set $Table = the current table name, we make the command to script out that current ($_) table with $_.script() - dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}
We pipe the output of that scripting function (|) to an out-file command, with the path that we want to use. If we wanted to, we could use a single file andu use -append; but I want one file per table, so we use a filepath plus $Table.txt (here, c:\$Table.txt)
That’s all there is to it. It seems like gibberish at first, but that’s why they call it “code”, right?
Let me take a moment here to throw a little love in Allen White’s direction (blog, @sqlrunr on Twitter). Allen also likes to talk about Powershell and SQL, and is also a really cool dude, so he’s yet another resource to check out on the path to enlightenment.
Happy days,
Jen McCown
* Sean says, “Don’t forget your curlies!”
“Recently I wanted a simple way to script out all Powershell objects”
Freudien slip there Jen? 🙂
Man, it took me 5 rereads and 2 days to figure out what was wrong with that sentence. Thanks Jamie!
ha, sorry jen! Hope I didn’t waste too much of your time! 🙂
Nono, not at all! I was just so CONFUSED…”What, what’s wrong with that? Freudian??” I kept looking for something inappropriate…
Fantastic article! Plus I think you slipped in a pseudo-quote from Zoolander, so extra points for that!
I can’t wait to try this out!
Glad we could help. But why MALE models?
(Are you kidding? I just told you that…)
# Many thanks to Brian Brooks for this script that help me so much.
#This one does not generate one per table but 1 per object type.
#You should have your list of intances in instance_list.txt
#The output is generated in .\sqlschemas folder
# LoopInstances
$InstanceListFile = “.\instance_list.txt”
$InstanceList = Get-Content $InstanceListFile
foreach ($InstanceName in $InstanceList)
{
# Configuration
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Replication”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.RMO”) | Out-Null
$MyScripter = New-Object “Microsoft.SqlServer.Management.SMO.Scripter”
# $mySrvConn = New-Object “Microsoft.SqlServer.Management.Common.ServerConnection”
# $mySrvConn.ServerInstance=$InstanceName
# $mySrvConn.LoginSecure = $false
# $mySrvConn.Login = “sa”
# $mySrvConn.Password = “”
$InstanceObject = New-Object “Microsoft.SqlServer.Management.SMO.Server” “$InstanceName”
$RepInstanceObject = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” “$InstanceName”
# $InstanceObject = New-Object “Microsoft.SqlServer.Management.SMO.Server” ($mySrvConn)
# $RepInstanceObject = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” ($mySrvConn)
$InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.ExtendedStoredProcedure”, “IsSystemObject”)
$InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.StoredProcedure”, “IsSystemObject”)
$InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.Table”, “IsSystemObject”)
$InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.View”, “IsSystemObject”)
$InstanceObject.SetDefaultInitFields(“Microsoft.SqlServer.Management.SMO.UserDefinedFunction”, “IsSystemObject”)
$MyScripter.Server = $InstanceObject
$ScripterOptions = $MyScripter.Options
# SetScriptOptions
$ScripterOptions.ToFileOnly = $True
$ScripterOptions.AppendToFile = $True
$ScripterOptions.ContinueScriptingOnError = $True
$ScripterOptions.LoginSid = $True
$ScripterOptions.AgentAlertJob = $True
$ScripterOptions.AgentNotify = $True
$ScripterOptions.Bindings = $True
$ScripterOptions.DriAll = $True
$ScripterOptions.Indexes = $True
$ScripterOptions.Permissions = $True
$ScripterOptions.SchemaQualify = $True
$ScripterOptions.Triggers = $True
$ScripterOptions.IncludeIfNotExists = $True
# SetSchemaPath
$CurrentDay = Get-Date -Format yyyyMMdd
$SchemaPath = “.\SQLSchemas\” + $CurrentDay + “\” + $InstanceName
if ((Test-Path $SchemaPath) -ne $True) {mkdir $SchemaPath | Out-Null}
# DisplayInstance
Write-Host “————————————————————”
Write-Host $InstanceName – (Get-Date)
Write-Host “————————————————————”
# CheckVersion
if ($InstanceObject.Information.VersionString.substring(0,2) -eq 10)
{
$InstanceObjectList = “Audits”,”BackupDevices”,”Credentials”,”CryptographicProviders”,”Databases”,”EndPoints”,”LinkedServers”,”Logins”,”ServerAuditSpecifications”,”Triggers”,”UserDefinedMessages”
$JobServerObjectList = “AlertCategories”,”Alerts”,”JobCategories”,”Jobs”,”OperatorCategories”,”Operators”,”ProxyAccounts”,”SharedSchedules”,”TargetServerGroups”,”TargetServers”
$DatabaseObjectList = “Assemblies”,”AsymmetricKeys”,”Certificates”,”DatabaseAuditSpecifications”,”Defaults”,”ExtendedProperties”,”FullTextCatalogs”,”FullTextStopLists”,”PartitionFunctions”,”PartitionSchemes”,”PlanGuides”,”Roles”,”Rules”,”Schemas”,”Synonyms”,”Triggers”,”UserDefinedAggregates”,”UserDefinedDataTypes”,”UserDefinedTableTypes”,”UserDefinedTypes”,”Users”,”XmlSchemaCollections”
}
if ($InstanceObject.Information.VersionString.substring(0,1) -eq 9)
{
$InstanceObjectList = “BackupDevices”,”Credentials”,”Databases”,”EndPoints”,”LinkedServers”,”Logins”,”Triggers”,”UserDefinedMessages”
$JobServerObjectList = “AlertCategories”,”Alerts”,”JobCategories”,”Jobs”,”OperatorCategories”,”Operators”,”ProxyAccounts”,”SharedSchedules”,”TargetServerGroups”,”TargetServers”
$DatabaseObjectList = “Assemblies”,”AsymmetricKeys”,”Certificates”,”Defaults”,”ExtendedProperties”,”FullTextCatalogs”,”PartitionFunctions”,”PartitionSchemes”,”Roles”,”Rules”,”Schemas”,”Synonyms”,”Triggers”,”UserDefinedAggregates”,”UserDefinedDataTypes”,”UserDefinedTypes”,”Users”,”XmlSchemaCollections”
}
if ($InstanceObject.Information.VersionString.substring(0,1) -eq 8)
{
$InstanceObjectList = “BackupDevices”,”Databases”,”LinkedServers”,”Logins”,”UserDefinedMessages”
$JobServerObjectList = “AlertCategories”,”Alerts”,”JobCategories”,”Jobs”,”OperatorCategories”,”Operators”,”TargetServerGroups”,”TargetServers”
$DatabaseObjectList = “Defaults”,”ExtendedProperties”,”FullTextCatalogs”,”Roles”,”Rules”,”UserDefinedDataTypes”,”Users”
}
# ScriptInstance
Write-Host Instance
foreach($Object in $InstanceObjectList)
{
Write-Host ” ” $Object
$ScripterOptions.FileName=$SchemaPath + “\Instance_” + $Object + “.sql”
if ((Test-Path $ScripterOptions.FileName) -eq $True)
{
del $ScripterOptions.FileName
}
$MyScripter.Script($InstanceObject.$Object)
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}
# ScriptJobServer
if ($InstanceObject.Information.Edition -ne ‘Express Edition’)
{
Write-Host JobServer
foreach($Object in $JobServerObjectList)
{
Write-Host ” ” $Object
$ScripterOptions.FileName=$SchemaPath + “\JobServer_” + $Object + “.sql”
if ((Test-Path $ScripterOptions.FileName) -eq $True)
{
del $ScripterOptions.FileName
}
$MyScripter.Script($InstanceObject.JobServer.$Object)
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}
}
# ScriptDatabases
$DatabaseFilter = $InstanceObject.Databases | where {$_.Name -ne “distribution”} | where {$_.Name -ne “master”} | where {$_.Name -ne “msdb”} | where {$_.Name -ne “ReportServer”} | where {$_.Name -ne “ReportServerTempDB”} | where {$_.Name -ne “tempdb”}
foreach($Database in $DatabaseFilter)
{
Write-Host $Database
foreach($Object in $DatabaseObjectList)
{
Write-Host ” ” $Object
$ScripterOptions.FileName=$SchemaPath + “\Database_” + $Database.name + “_” + $Object + “.sql”
if ((Test-Path $ScripterOptions.FileName) -eq $True)
{
del $ScripterOptions.FileName
}
$ScripterOptions.Permissions = $True
$MyScripter.Script($InstanceObject.Databases[$Database.name].$Object)
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}
foreach($Object in “ExtendedStoredProcedures”,”StoredProcedures”,”Tables”,”Views”,”UserDefinedFunctions”)
{
Write-Host ” ” $Object
$ScripterOptions.FileName=$SchemaPath + “\Database_” + $Database.name + “_” + $Object + “.sql”
if ((Test-Path $ScripterOptions.FileName) -eq $True)
{
del $ScripterOptions.FileName
}
$ObjectFilter = $InstanceObject.Databases[$Database.name].$Object | where {$_.IsSystemObject -eq $False}
if ($ObjectFilter)
{
$ScripterOptions.Permissions = $True
$MyScripter.Script($ObjectFilter)
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}
}
}
# ScriptReplication – Added after all SMO scripting to avoid bug detected in losing the database context
$RepScriptOptions = [Microsoft.SqlServer.Replication.scriptoptions]::Creation `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions
Write-Host Replication
foreach($ReplicatedDatabase in $RepInstanceObject.ReplicationDatabases)
{
$ScriptFileName=$SchemaPath + “\Replication_” + $ReplicatedDatabase.Name + “.sql”
if ((Test-Path $ScriptFileName) -eq $True)
{
del $ScriptFileName
}
foreach($Publication in $ReplicatedDatabase.TransPublications)
{
Write-Host ” ” $Publication.Name
$Publication.Script($RepScriptOptions) >> $ScriptFileName
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}
foreach($Publication in $ReplicatedDatabase.MergePublications)
{
Write-Host ” ” $Publication.Name
$Publication.Script($RepScriptOptions) >> $ScriptFileName
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}
}
}
I’ll say it again, Sri: There are SMO solutions (like you posted), but there’s definitely efficiency, elegance, and convenience to having everything you need in a SINGLE line of code:
dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}
All you need to do is run this same script in another folder (Views, UserDefinedFunctions, etc) to get those objects. I like this way better 🙂
Amen to elegance but when u want to script all instances in your environment , including server level objects and each and every db object , I struggle to find elegant solutions. Let me know if you find one.
Pingback: Jen McCown (aka SQL Awesomesauce) posts many PowerShell video links | sqlmashup
Sri’s solution is OK. It’s a lot better than the general run of scripting routines one sees. It is doing something a lot different to your one-liner, and obviously a lot more useful. The trouble is that there are a lot of different demands for scripting. Do you want, for example, dependent objects? (you generally do with tables, for example, but occasionally not) Do you want the extended properties (fancy stripping all your comments out of your database accidentally?)
It also runs in ordinary powershell 2 whereas yours only runs in SQLPS which is a cobbled powershell 1. This is fine for one-liners, but many scripts tend to be complex schedule-based things for doing nightly saves into Source-control or continuous integration, that use the PS2 features.
Try replicating the functionality in Siri’s code and you’ll have a lot of ‘one-liners!
@Siri. Try using the EnumObjects function. It is a bit tricky to use, but it will make your code a LOT shorter!
@Jen. It wasn’t a freudian slip, just an ordinary one.
Phil,
You absolutely have some valid points, but remember the start of the post: I wanted a simple way to script out database objects. The one-line script does suffer from the weaknesses you point out, but it met my meager requirements at the time, and I’ll stand by that.
And, it could well have been a Freudian slip…if you felt about Powershell like some do.. O.o
-J