Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Generating SQL Scripts using Windows PowerShell


By:   |   Read Comments (20)   |   Related Tips: More > PowerShell

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Edwin Sarmiento 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, December 17, 2013 - 5:53:52 PM - bass_player Back To Top

Hi Guido,

Do you have a database named Northwind? What version of SQL Server are you using?


Monday, December 16, 2013 - 1:53:19 PM - Guido Back To Top

$dbs["Northwind"].Script() gives me an error:


Exception calling "Script" with "0" argument(s): "Script failed for Database 'Northwind'. "
At line:1 char:25
+ $dbs["Northwind"].Script <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

I copy pasted the command from this website, only changed the datasource part to connect to my sql server.

Anybody has any ideas ?


Saturday, May 05, 2012 - 1:57:57 AM - gingendes Back To Top

When i tried scripting data with this, it says "This Method doesnot support Scripting Data", any cure?

 


Wednesday, March 07, 2012 - 9:54:16 PM - Graham Back To Top

Thanks Edwin

Nice article. Powershell comes into its own area when applying this sort of thing to a few hundred databases and instances.

Regards

Graham


Friday, February 25, 2011 - 3:06:12 PM - Tom Powell Back To Top

Edwin,

I want to thank you for taking the effort to write this article.  I had a litle trouble with one of the scripts too but it helped me dig into the code and figure out what was going on.  I added a few bells and whistles and had a great time doing it.  I'm going to blog it but I'd like to credit your post too.  Once I get it ready you can, if you wish, view the reference here:  Philergia.WordPress.com.

Thanks for the help! 


Friday, March 19, 2010 - 3:47:41 PM - stittdba Back To Top

[quote user="debettap"]

When the ScriptDrops option is true, only drop scripts are generated. If you want create scripts, this option must be false:

$scrp.Options.ScriptDrops = $False

If you want both drops and creates, you need to Script with this option set to false then Script again with this option set to true.

 

[/quote]

When I set the ScriptDrops to False, the create statements for the views and procedures are not included. However, when ScriptDrops is set to True, the views and procedures are part of the drop statements. How can I get the views and procedures to be part of the create statements. I had hoped the WithDependencies option would catch them, but SSMS doesn't show that the views and procedures have a dependency on their corresponding tables. Any ideas?


Thursday, March 18, 2010 - 2:04:13 PM - stittdba Back To Top

[quote user="debettap"]

When the ScriptDrops option is true, only drop scripts are generated. If you want create scripts, this option must be false:

$scrp.Options.ScriptDrops = $False

If you want both drops and creates, you need to Script with this option set to false then Script again with this option set to true.

 

[/quote]

Some things are so obvious, but yet they escape you. Thanks.


Thursday, March 18, 2010 - 1:40:51 PM - debettap Back To Top

When the ScriptDrops option is true, only drop scripts are generated. If you want create scripts, this option must be false:

$scrp.Options.ScriptDrops = $False

If you want both drops and creates, you need to Script with this option set to false then Script again with this option set to true.

 


Thursday, March 18, 2010 - 1:09:33 PM - debettap Back To Top

@stittdba - Nice catch. Thanks for that.


Tuesday, March 16, 2010 - 4:04:02 PM - hallozen Back To Top

@debettap  & stittdba

 

Thanks that worked!


Thursday, March 11, 2010 - 3:02:10 PM - stittdba Back To Top

[quote user="debettap"]

Cast it to a collection of SqlSmoObject and it works. :-) 

$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[$dbs["AdventureWorks"].Tables)

[/quote]

You need one more closed bracket

$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$dbs["AdventureWorks"].Tables)


Thursday, March 11, 2010 - 12:25:48 PM - debettap Back To Top

Cast it to a collection of SqlSmoObject and it works. :-) 

$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]$dbs["AdventureWorks"].Tables)


Wednesday, March 03, 2010 - 3:08:52 PM - samshah101 Back To Top

has anyone found a cure to this.

 


Friday, February 26, 2010 - 4:30:04 PM - hallozen Back To Top

i am also receiving the ambiguous error. can someone take a look?

 windows server 2008 R2
SQL Server 2008 Sp1
Powershell 2.0

it's the last line that gets the error:


PS C:\> [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
PS C:\> $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
PS C:\> $dbs=$s.Databases
PS C:\> $dbs["AdventureWorks"].Script() | Out-File C:\temp\myscript.SQL
PS C:\> $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
PS C:\> $scrp.Options.AppendToFile = $True
PS C:\> $scrp.Options.ClusteredIndexes = $True
PS C:\> $scrp.Options.DriAll = $True
PS C:\> $scrp.Options.ScriptDrops = $True
PS C:\> $scrp.Options.IncludeHeaders = $True
PS C:\> $scrp.Options.ToFileOnly = $True
PS C:\> $scrp.Options.Indexes = $True
PS C:\> $scrp.Options.WithDependencies = $True
PS C:\> $scrp.Options.FileName = "C:\temp\myscript.SQL"
PS C:\> $scrp.Script($dbs["AdventureWorks"].Tables)
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At line:1 char:13
+ $scrp.Script <<<< ($dbs["AdventureWorks"].Tables)
    + CategoryInfo          : NotSpecified: (:) [, MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

 

 

thanks in advance


Wednesday, January 27, 2010 - 2:39:36 PM - bass_player Back To Top

Can you post your entire script?


Wednesday, January 27, 2010 - 1:14:43 AM - samshah101 Back To Top

hi

what did you do to get over this.  i am getting the same error and cant find a solution.  any help would be very handy.

Multiple ambiguous overloads found for "Script" and the argument count: "1".


Saturday, October 17, 2009 - 4:02:56 PM - bass_player Back To Top

I totally agree with you. Learning Windows PowerShell is not as easy as it seems as you have to deal with a ton of .NET-related objects. Which is why MSSQLTips is here to make the learning process a bit easier than doing it on your own. But it's like learning how to walk - it's hard to start but you have to. Microsoft is defining the next wave of server products to include manageability using PowerShell (they've already started out in Exchange 2007) so learning PowerShell will enable any administrator to write scripts that can manage just about any Microsoft server product, not just SQL Server. And since SQL Server 2005 and higher has been built on top of the .NET Framework with SMO, you can take advantage of the exposed APIs to administer SQL Server by using PowerShell

BTW, if you are looking for a nice IDE for writing PowerShell scripts, check out PowerGUI from Quest Software. It's a free IDE and script editor for Windows PowerShell that enables you to do stuff such as debug your scripts. 

http://www.powergui.org/index.jspa


Friday, October 02, 2009 - 12:03:12 PM - Repriser Back To Top

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

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

Learn more about SQL Server tools