PowerShell and SQL Server: Script all Tables

This is a script that I found/put together/re-arranged that will script out all of the tables from a database - in this case, Adventureworks2008. You need to change the BWOODY1 part to the name of your server, and the SQL2K8 part to your Instance name. You can change the database name as well, of course, and a better method would be to make a function out of this and feed those variables in.

There are other ways to do this, but this script illustrates setting some of the scripting options. You can look up the SMO model for all of them.

As always, test this script out thoroughly, and understand what you're doing before you put this on a production system. Also, don't run with scissors, and don't open attachments from people you do not know:

 # Script all tables in the AdventureWorks2008 database
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'
 $db = $s.Databases['AdventureWorks2008']
 $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
 $scrp.Options.AppendToFile = $True
 $scrp.Options.ClusteredIndexes = $True
 $scrp.Options.DriAll = $True
 $scrp.Options.ScriptDrops = $False
 $scrp.Options.IncludeHeaders = $True
 $scrp.Options.ToFileOnly = $True
 $scrp.Options.Indexes = $True
 $scrp.Options.WithDependencies = $True
 $scrp.Options.FileName = 'C:\TEMP\AdventureWorks2008Tables.SQL'