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
Clint Huijbers' Blog 
Excellent.
good script, makes life easier – do you have a script to reassemble all the individual object scripts into one ?
You might need to modify the PowerShell script for that, look for this line:
Mooi script, werkt prima
Pingback: PowerShell: Delete old files based on a given retention period (days) #maintenance | Clint Huijbers' Blog