PROBLEM: I need an easy way to script out all the tables from a database in MS SQL 2000 and above.
SOLUTION: Like anything in programming, there are a thousand ways to skin a cat. Here, I’ll show you the simplest way I found to meet this request.
Go ahead and open a new session of PowerShell. You can type or copy and paste in your PS session window all the code in this solution. We will start by loading the SMO assembly.
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")|out-null
Next, we’ll create an instance of SMO server object, database object, and scripter object
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" "."
$db = new-object("Microsoft.SqlServer.Management.SMO.Database")
$scr = new-object("Microsoft.SqlServer.Management.SMO.Scripter")
We then assign the instance of MS SQL Server we want the scripter to use. We wrap it up by calling the Script() method with an SQL SMO collection of objects to be script out –in this case, all tables.
$scr.Server = $srv $scr.Script($srv.Databases["AdventureWorks"].tables)
That’s it! The simplest way to script out all tables from a database with PowerShell. In a future article, I’ll expand from this solution by writing a script that will take parameters, use SMO Scripter Options, filter tables to be script out, and then some.
