Back in 2009, I wrote an article about how to generate SQL scripts using Windows PowerShell and Server Management Objects (SMO) using the Script() method. One thing that you need to consider when using the Script() method is making sure that you have taken into account all of the database objects as well as their related objects. Take, for example, the Table object (or Table class according to SMO). A Table object consists of a collection of columns. Each column consists of several properties that can be considered database objects such as primary and foreign keys, constraints, etc. This means that for every column, you need to iterate thru all the possible constraints. Same thing with triggers and indexes. And we’re just looking at one database object here with multiple dependencies – the Table.

This is something that I needed to deal with when one of my customers wanted me to fix a process that included running a backup and restoring it on another server as well as truncating the tables.  They simply needed to clone their database structure for testing purposes. I highlighted the fact that the backup/restore/table truncate process is utilizing a lot of unnecessary resources – network, disk and CPU – when all they needed was the database structure. That’s where I recommended using database creation scripts. It’s lightweight and does not utilize unnecessary resources. While I’m a big fan of process automation, I emphasize the need for proper definition of the process as well as making the process as simple as I possibly can. This PowerShell script is just one of the steps included in that process.

$sourcePath="C:temp" #Variable to contain location of SQL scripts

$s=new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"

#Assign object to the database selected
$dbs=$s.Databases

#Script database creation - adding GO statement
$dbs["$database"].Script() +"`r`nGO`r`nUSE [$database]`r`nGO`r`n" | Out-File $sourcePath$database.sql -append

#Generate script for all tables, excluding system tables
foreach ($tablesin$dbs["$database"].Tables | where {$_.IsSystemObject -eq$false})
{
$tables.Script() +"`r`nGO`r`n" | Out-File$sourcePath$database.sql -append

#Generate script for all indexes in the specified table
foreach($indexin$tables.Indexes)
{
$index.Script() +"`r`nGO`r`n" | Out-File$sourcePath$database.sql -append
}

#Generate script for all triggers in the specified table
foreach($triggerin$tables.Triggers)
{
$trigger.Script() +"`r`nGO`r`n" | Out-File$sourcePath$database.sql -append
}

#Generate Default Constraints on columns that have them
#Iterate thru all the columns in the table
foreach($columnin$tables.Columns)
{
#Iterate thru all the default constraints in the columns
foreach($defaultin$column.DefaultConstraint)
{
#If there are default constraints, script out the ADD CONSTRAINT command
if ($default-ne$null)
{
$default.Script() +"`r`nGO`r`n" | Out-File$sourcePath$database.sql -append
}
}
}

}

You can literally dig deeper on the different properties of the Table class to generate the corresponding script for the objects that you wish to create. Happy scripting!