Powershell Day 26

Day 26 Generate script

Generate script for sql server objects is very simple, there are different ways we can generate the sql server objects includes – database, table, trigger, stored procedure…

  1. using SqlPS –MiniShell:

We have already discussed about mini shell in our blog post Day 16 and Day 17, were we discussed about how simple is to work with sqlps, and invoke-sqlcmd command just by going to that directory and working on that object. Like databases/tables….


To start it with SQLPS



You can move to folders and go till the databases/tables


PS SQLSERVER\SQL\Servername\InstnaceName\Databses\Table\>


Here if you see dir get table “Table1”


You can select that table1 and get-item into a variable


PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a=get-item dbo.Table1

PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a.script()


Method Script() is good to go for scripting the object. This can be applicatable to any object of the sqlserver shell


To Get the script output into a file just redirect to any file as


PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a.script() |out-file c:\table_table1.sql


PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a.script() >c:\table_table1.sql


You can use any redirect operator.


This will be the same script which you will get when you right click the object from SSMS and select “Generate Script”.


We can also generate the script for all the objects and their dependent objects. Using loops


Below script will give all the script of all tables from current database


PS SQLSERVER:\SQL\Servername\InstnaceName\Databases\Table\> foreach ($tbl in Get-ChildItem) {$tb1.script()}


Similarly, to get the script into a flat file.

PS SQLSERVER:\SQL\Servername\InstnaceName\Databases\Table\> foreach ($tbl in Get-ChildItem) {$tb1.script() >>c:\tb1.sql}



PS SQLSERVER:\SQL\Servername\InstnaceName\Databases\Table\> dir | %{$_.Script() | out-file c:\Tables.txt -append}


As I mansion several times, Means once you know the concept and cmdlets you can achieve the result with several ways.


To get the properties , lots of lots of info.


Gm (get-member)


$tb1 |gm



  1. Using SMO


For scripting the object need to create an object of type


$scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver)




A great blog by

Ed Wilson, Microsoft Scripting Guy

Here it shows to script all the objects also with dependent objects as well.


Here I am showing just a part of sciprt which generate the script of table objects ( we can add all the objects like sp, views… and on variable $objects += and make it in loop.) also can pass several parameters. Please see the link for more information.

PS C:\> [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null

PS C:\> $server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “SqlServer\InstanceName”

PS C:\> $db=$SMOserver.Databases[“Db1”]

PS C:\> $object=$db.Tables

PS C:\> $scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($server)

PS C:\> $scriptr.Options.Indexes=1

PS C:\> $scriptr.Options.WithDependencies=1

PS C:\> $scriptr.Options.FileName=”c:\tables.sql”

PS C:\> $scriptr.Script($object)

Here you could see the option “WithDependencies” will generate script with all the dependencies on it.


  1. Script the Replication


As we discussed the replication in our RMO blog post at day 25. Here we will discuss to generate the script of Replication. There is a great blog by Anthony Brown



Like “scripter” object  in SMO, RMO scripting requires “Microsoft.SqlServer.Replication.ScriptOptions” object.


Here is the detail information about ScriptOptions



So for Generating script for existing replication. We can just create an object for the ReplicationDatabases after connecting to the instance. And then create an object of publiser and subsriber

Finally generate the script for them.


There is another good blog for


 new-object Microsoft.SqlServer.Management.Smo.scripter

new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions


I will try to keep this blog updated about Generate scripting.

This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s