Generating SQL Scripts using Windows PowerShell

By: Edwin Sarmiento | Comments (20) | Related: > PowerShell

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.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1"  

$dbs=$s.Databases 

$dbs["Northwind"].Script()

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.

$dbs["Northwind"].Script() | Out-File D:\PSScripts\Northwind.sql

The results of the generated SQL script is shown below

CREATE DATABASE [Northwind] ON  PRIMARY  
( NAME = N'Northwind', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind.mdf' , 
SIZE = 3456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
 LOG ON  
( NAME = N'Northwind_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind_1.ldf' , 
SIZE = 4224KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
 COLLATE SQL_Latin1_General_CP1_CI_AS 
EXEC dbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
BEGIN 
EXEC [Northwind].[dbo].[sp_fulltext_database] @action = 'disable' 
END 
ALTER DATABASE [Northwind] SET ANSI_NULL_DEFAULT OFF  
ALTER DATABASE [Northwind] SET ANSI_NULLS OFF  
ALTER DATABASE [Northwind] SET ANSI_PADDING OFF  
ALTER DATABASE [Northwind] SET ANSI_WARNINGS OFF  
ALTER DATABASE [Northwind] SET ARITHABORT OFF  
ALTER DATABASE [Northwind] SET AUTO_CLOSE OFF  
ALTER DATABASE [Northwind] SET AUTO_CREATE_STATISTICS ON  
ALTER DATABASE [Northwind] SET AUTO_SHRINK OFF  
ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS ON  
ALTER DATABASE [Northwind] SET CURSOR_CLOSE_ON_COMMIT OFF  
ALTER DATABASE [Northwind] SET CURSOR_DEFAULT  GLOBAL  
ALTER DATABASE [Northwind] SET CONCAT_NULL_YIELDS_NULL OFF  
ALTER DATABASE [Northwind] SET NUMERIC_ROUNDABORT OFF  
ALTER DATABASE [Northwind] SET QUOTED_IDENTIFIER OFF  
ALTER DATABASE [Northwind] SET RECURSIVE_TRIGGERS OFF  
ALTER DATABASE [Northwind] SET  DISABLE_BROKER  
ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
ALTER DATABASE [Northwind] SET DATE_CORRELATION_OPTIMIZATION OFF  
ALTER DATABASE [Northwind] SET TRUSTWORTHY OFF  
ALTER DATABASE [Northwind] SET ALLOW_SNAPSHOT_ISOLATION OFF  
ALTER DATABASE [Northwind] SET PARAMETERIZATION SIMPLE  
ALTER DATABASE [Northwind] SET READ_COMMITTED_SNAPSHOT OFF  
ALTER DATABASE [Northwind] SET  READ_WRITE  
ALTER DATABASE [Northwind] SET RECOVERY FULL  
ALTER DATABASE [Northwind] SET  MULTI_USER  
ALTER DATABASE [Northwind] SET PAGE_VERIFY TORN_PAGE_DETECTION   
ALTER DATABASE [Northwind] SET DB_CHAINING OFF

Scripting tables

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

#Generate script for all tables

foreach ($tables in $dbs["Northwind"].Tables) 
{
    $tables.Script() + "`r GO `r " | out-File D:\PSScripts\Scripts.sql -Append
}

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

#Generate script for all tables

foreach ($tables in $dbs["Northwind"].Tables) 
{
    $tables.Script() + "`r GO `r " | out-File D:\PSScripts\Scripts.sql -Append

    #Generate script for all indexes in the specified table
    foreach($index in $tables.Indexes)
    {
        $index.Script() + "`r GO `r" | out-File D:\PSScripts\Scripts.sql -Append
    }
}

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.

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
   [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
   [Picture] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
 GO  
CREATE NONCLUSTERED INDEX [CategoryName] ON [dbo].[Categories]  
( 
   [CategoryName] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
GO

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.

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

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.

$scrp.Options.AppendToFile = $True 
$scrp.Options.ClusteredIndexes = $True 
$scrp.Options.DriAll = $True 
$scrp.Options.ScriptDrops = $True  
$scrp.Options.IncludeHeaders = $True 
$scrp.Options.ToFileOnly = $True 
$scrp.Options.Indexes = $True 
$scrp.Options.WithDependencies = $True 

$scrp.Options.FileName = "D:\PSScripts\NWind.SQL"

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

$scrp.Script($dbs["Northwind"].Tables)

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

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1" 

$dbs = $s.Databases
$dbs["Northwind"].Script() | Out-File D:\PSScripts\NWind.SQL

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.IncludeHeaders = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True

$scrp.Options.FileName = "D:\PSScripts\NWind.SQL"
$scrp.Script($dbs["Northwind"].Tables)

Next Steps

Check out these other PowerShell Tips

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Thursday, March 18, 2010 - 1:09:33 PM - debettap	Back To Top (5077)
@stittdba - Nice catch. Thanks for that.

Wednesday, January 27, 2010 - 2:39:36 PM - bass_player	Back To Top (4800)
Can you post your entire script?

Friday, October 2, 2009 - 12:03:12 PM - Repriser	Back To Top (4118)
Not practical. Spending the time to learn Power Script, you could have had it done in T-SQL. This script has no particular rules. It is not practical to script for a new task. You will be spending weeks to find out how to write something and without a debugger to help.

Tuesday, September 22, 2009 - 8:40:08 AM - bass_player	Back To Top (4075)
I copied the exact same script and it worked perfectly fine. Check that you have the Northwind database created in your SQL Server instance. You also need to check your instance name in Line 4 and make sure that you have the correct folders and path in lines 7 and 21

Tuesday, September 22, 2009 - 7:21:34 AM - lkoravi	Back To Top (4074)
I tried the script at the end and got this error: Multiple ambiguous overloads found for "Script" and the argument count: "1". At C:\Documents and Settings\riyer\My Documents\scripts\scripter.ps1:22 char:13 + $scrp.Script <<<