Free SQL Server monitoring resources

Problem
In a previous tip on SQL Script Generation Programmatically with SMO, you've seen how you can use SMO to generate SQL scripts programmatically. In this tip I will cover how to generate scripts using Windows PowerShell.

Solution
SQL scripting in SMO is controlled either by the Scripter object and its child objects, or the Script method on individual objects as highlighted in the previous tip. We will be using the same approaches to generate SQL scripts using Windows PowerShell.

Using the Script() method

Similar to what we have done in previous Windows PowerShell tips with SQL Server, we will create an instance of the Server object and connect to it. And as I've always mentioned in all the previous Windows PowerShell tips I've written, the only aspect of the code that we will change from the scripts in the previous tips is the last line, i.e. adding new properties or methods for the new objects we will be working with, highlighting the power and simplicity of Windows PowerShell. In this particular example, we just introduced the Script() method from the database object collection, selecting the Northwind as the target database.

That's about it. The Script() method will generate the SQL script for creating the Northwind database - all in a single line of code. Of course, this would not mean anything at all to us until we send the output to a script file. We can then pipe the results of calling the Script() method to a file using the Out-File cmdlet as we did in another previous tip.

The results of the generated SQL script is shown below

Scripting tables

We can then iterate thru the Tables collection in the Northwind database and call the Script() method once more

Notice that I have done three things here. First, I have included the carriage return symbol - `r - just so I can display the results properly. The Script() method returns a StringCollection where each string in the collection contains one of the statements generated by the script. Second, I have appended the GO statement to treat each CREATE TABLE statement as a batch. Since the Script() method will generate CREATE TABLE statements, it wouldn't be much of an issue even without the GO statement. But if it will generate scripts for other database objects, like views and stored procedures, you definitely need to include a GO statement to treat the individual CREATE statements as a batch. And third, I used the -Append parameter of the Out-File cmdlet to append the output of the Script() method to the existing file.

Scripting indexes

Since we're already generating scripts for table creation, we might as well generate the scripts for the corresponding indexes within tables. We just need to iterate thru the Indexes collection, again calling the Script() method and appending the results in the output file

A section of the resulting SQL script is shown below. Notice how you can easily generate SQL scripts with just a few lines of code in Windows PowerShell with SMO.

Using the Scripter object

Another option to generate SQL scripts is by using the Scripter object. The Scripter object is the overall, top-level object for managing scripting operations in SQL Server. As it is a full blown object, it has a lot more capabilities than the Script() method. For example, it has the ability to discover relationships between objects and provide you with more scripting options similar to using SQL Server Management Studio.

In our Windows PowerShell script, we will be introducing this new object using the Microsoft.SqlServer.Management.Smo.Scripter namespace.

Where $s is an instance of the Server object we have previously defined. Once we have defined Scripter object, we can then specify the different Options property. These properties define how the Scripter object will generate the SQL script. Below are some of the options I took from SQL Server Management Studio as an example.

Here's a brief description of the properties mentioned in the script

• AppendToFile - specifies whether to append the script to the output file or overwrite it. It's the equivalent of what we did with the Out-File cmdlet using the -append parameter
• ClusteredIndexes - specifies whether to include the clustered index definitions
• DriAll - specifies whether to include the DRI - declarative referential integrity - objects
• ScriptDrops - specifies whether to include the IF EXISTS..DROP statements
• IncludeHeaders - specifies whether to include header information llike the date and time the script was generated
• ToFileOnly - specifies whether to simply generate the script to a file or store it in a string as an output as well
• Indexes - specifies whether to include indexes
• WithDependencies - specifies whether to include all other dependent objects in the script

The key property here is the WithDependencies property as you can then generate scripts for all other objects, such as views and stored procedures, that are dependent on the object that you want to script out. Unlike when simply using the Script() method where we need to explicitly call all the other object collections to generate the corresponding scripts, this property tells the Scripter object to simply look at the relationships and object dependencies as the basis for generating the script.

Finally, we need to call the Script() method of the Scripter object, passing the database object collection that we want to generate scripts for

The complete code listing for using the Scripter object is shown below.

• Find SQL Server Instances Across Your Network Using Windows PowerShell
• Retrieve Default SQL Server Backup Folder using PowerShell
• Connecting to SQL Server via Windows PowerShell with SQL Server authentication
• Backup SQL Server Databases with a Windows PowerShell Script
• Checking SQL Server Agent jobs using Windows PowerShell
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback