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"

sql-agent-powershell-step-script-all-objects

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



Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s