Generating SQL Scripts using Windows 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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *