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

 

PS SQLSERVER C:\>

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

or

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}

 

Or

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

 

 

2. Using SMO

 

For scripting the object need to create an object of type

 

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

$scriptr.Script(“Object(s)”)

 

 

A great blog by

Ed Wilson, Microsoft Scripting Guy

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

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx

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.

 

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

http://sqlblogcasts.com/blogs/antxxxx/archive/2011/04/04/scripting-replication-with-powershell.aspx

 

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

 

Here is the detail information about ScriptOptions

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.scriptoptions.aspx

 

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

http://www.scarydba.com/2010/03/01/powershell-for-batch-operations/

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

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

 
http://get-powershell.com/post/2011/04/04/How-to-Package-and-Distribute-PowerShell-Cmdlets-Functions-and-Scripts.aspx

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

About these ads