Data Podcast

By Rajib Bahar at June 15, 2017 02:32
Filed Under: Data, Data Podcast, Database, Podcast, SQL, SQL Server, Big Data, Data Science, Analytics

Last few months Shabnam, & I were working on creating a podcast. Our podcast brings industry experts working in various Data practices. We focus on topics related to Big Data, Data Science, Database technologies, RDBMS. Many thanks to our colleagues & friends for their support in this initiative.


Here are links to our podcast:

Soundcloud ->

iTunes ->

Combining SMO and Powershell to Generate SQL Database Schema

By Rajib Bahar at December 08, 2009 10:49
Filed Under: .NET, SQL

There are times we find the need to generate the database schema. In SQL Server, it can be easily done using the graphical wizards in the Management Studio. I haven't found a way to script it to this day.


However, one alternative solution to this is to combine .NET programmability feature in powershell, and SMO. With this approach you can setup a powershell script job to automate your team's database build process.


Here are some basic assumptions before reading this post:

1. SQL Server 2008 is installed

2. Powershell is installed

3. SMO is in the GAC (Global Assembly Cache) or you know how to register it there

4. AdventureWorks is loaded in the database


Here are the steps I took to generate script against AdventureWorks database:


First of all, I went to management studio and right clicked on the AdventureWorks database to "Start Powershell"


PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\1...


At this point, the SMO object was loaded using .NET reflection technology. Next we declare $srv variable and assign the local SQL Server 2008 instance to it. Then we assign the AdventureWorks database to the $db variable. The database object has an overloaded method namely Script(). We need to invoke that method to generate the database script. See the output below as the script is run.


PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $srv = new-object("Microsoft.SqlServer.Management.Smo.Server") "(local)\sql2k8"
PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db = $srv.Databases["AdventureWorks"]

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db

WARNING: column "Owner" does not fit into the display and was removed.

Name                        Status          Recovery Model   CompatLvl        Collation
----                           ------          --------------           ---------            ---------
AdventureWorks       Normal          Simple                 100            SQL_Latin1_General_CP1_CI_AS

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db.Script()

( NAME = N'AdventureWorks_Data', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH =16384KB )
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 18432KB , MAXSIZE = 2048GB , FILEGROWTH = 1638
4KB ) COLLATE SQL_Latin1_General_CP1_CI_AS
    EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'


The limitation we run with the above approach is that it doesn't include the objects such as tables, stored procedures, and functions in the script. That's why we have to write a loop to iterate all the tables, procedures, checks, primary, functions, etc. Each of those classes have the Script() method and we can invoke them as we need it. 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> for ($i=0; $i -lt $db.Tables.Count; $i++) {$db.Tables[$i].Script()}

Yes, there are more gotchas. :(


So far, we have looked into resolving this issue using 1 of the overloaded Script() method. The 2nd version of the overloaded method expects ScriptingOptions as one of the parameter. Here is how we would declare them and the options they give us.

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $sc = new-object("Microsoft.SqlServer.Management.Smo.ScriptingOptions")

Here is a quick list of properties we can set on the $sc (ScriptingOptions) object.

            $sc.AppendToFile = 0;
            $sc.Bindings = 1;
            $sc.Default = 1;
            $sc.DdlBodyOnly = 1;
            $sc.DriAll = 1;
            $sc.DriAllConstraints = 1;
            $sc.DriAllKeys = 1;
            $sc.DriPrimaryKey = 1;
            $sc.IncludeDatabaseContext = 1;
            $sc.IncludeDatabaseRoleMemberships = 1;
            $sc.IncludeHeaders = 1;
            $sc.IncludeIfNotExists = 1;
            $sc.Indexes = 1;
            $sc.LoginSid = 1;
            $sc.PrimaryObject = 1;
            $sc.Permissions = 1;


 Depending upon your need, you can set the target server version, and the output file properties as well.

VLDB - What's the largest database have you worked with?

By Rajib Bahar at January 19, 2009 15:52
Filed Under:
I am asking this question to all of my SQL Server colleagues. There is no right or wrong answer to this. I am interested in learning the experience others have with VLDB systems. Please share your stories. I will compile the responses and make a newer blog entry. That new entry will have your story and link back to your blog if you have any. Feel free to send your responses via facebook, twitter, plaxo, linkedin, livespace, and email. Thanks

Tag cloud

Month List