Welcome to Day 27 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

One of the tasks that we are frequently called upon to perform is to script out objects from one server and to apply them to another. Fortunately, PowerShell makes this easy.

Virtually every object in the SMO library has a Script method. Actually, they have two, one without parameters and one that accepts a ScriptOptions collection for how to control the scripting. Want to script out an entire database?  Navigate to the database, and call the Script() method. Need a procedure? Navigate to the procedure, and call the Script() method. Views? Functions? Tables? Indexes? They all have a Script() method that you can use.

What’s really neat is that with every version of SQL Server, the SMO libraries are updated to handle the new features. For instance, SQL Server 2012 introduced a ColumnStore index. The Script() method for the index can script it out.

Have you ever written a T-SQL script to script out all of the indexes in a database? It can get quite long (the incomplete version that I have is over 300 lines of code)… and are you certain that it handles everything correctly? Here’s my PowerShell script that will script out any index. Over half of this script is parameters, comments and code to verify parameters and to get it ready to send the output to a file. The actual scripting of all of the indexes is less than 40 lines… let’s see you do that in T-SQL. As an added bonus,  the next version of SQL Server will probably include clustered ColumnStore indexes… this script will generate those without any modifications, since the logic to generate the script is in the SMO library, not in PowerShell (the only thing that I might have to change is the method of ensuring that the SMO libraries are loaded… that keeps changing from version to version). Here is my PowerShell script:

[codesyntax lang=”powershell” strict=”yes”]

[/codesyntax]