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):
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