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"

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.

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.

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.

Two weeks ago I struggled to get the "AdventureWorks 2008" to work on my "SQL 2008 R2" server. I ended up building a 2005 version of it. It appears they have released a beta version of it for the R2 release at CodePlex. Help yourself at your own learning venture.