Use PowerShell to script all database objects

Last week I started working with PowerShell for the first time.
My goal was to create a script that would somehow script all indexes (CREATE-statements) in specified databases.

When I was done Google-ing for all kind of solutions, I combined all sorts of existing PowerShell scripts. The resuls was a single PowerShell script that simply scripts all database objects within the specified database (like the ‘Generate Scripts‘-option does in SSMS/SSDT)!

Modify the script to adjust a few settings!
In the header section, you may want to change the destination folder for the output scripts. Look for:
$SavePath = "C:\PowerShell\SQLScripts\" + $($dbname) + "\" + $DateFolder

Another things is that you’ll need to define which databases on which server needs to be scripted in the bottom section of the PowerShell script:

#Execute function for all PM's user databases:

Script-DBObjectsIntoFolders '.' 'AdventureWorks2012'
Script-DBObjectsIntoFolders 'localhost' 'AdventureWorks2012'
Script-DBObjectsIntoFolders 'servername\instancename' 'AdventureWorks2012'

How to execute?:
Execute this statement in Command (CMD) or via a SQL Agent Job (PowerShell step):

PowerShell "C:\PowerShell\ScriptAllObjectsInDatabase.PS1"


The Result?:
It creates a new folder every time it is executed, the name of the folder will be a timestamp.

Schedule the thing!

I used the command (CMD) steptype of SQL Agent, because I simply didn’t got it working as a PowerShell steptype:

Download the PowerShell script here: ScriptAllObjectsInDatabase
Don’t forget to save it as ‘ScriptAllObjectsInDatabase.PS1‘!

*** UPDATE ***
In case you would also like to script XML Indexes, add these code-lines to the script on the right locations:

$scriptr.Options.XmlIndexes = $True
$scriptDrop.Options.XmlIndexes = $True

5 thoughts on “Use PowerShell to script all database objects

  1. Pingback: PowerShell: Delete old files based on a given retention period (days) #maintenance | Clint Huijbers' Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s