PowerShell and SQL Server: Script all Tables

PowerShell and SQL Server: Script all Tables

  • Comments 2

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'
 
$scrp.Script($db.Tables)
Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
  • When I customize the code as

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")|out-null

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

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

    $db = $s.Databases['hubdb']

    $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 = 'd:\AdventureWorks2008Tables.SQL'

    $scrp.Script($db.Tables)

    It throws the following error, any idea?

    PS C:\Users\Daniel.Wu> D:\scripts\c.ps1

    Multiple ambiguous overloads found for "Script" and the argument count: "1".

    At D:\scripts\c.ps1:17 char:13

    + $scrp.Script <<<< ($db.Tables)

       + CategoryInfo          : NotSpecified: (:) [], MethodException

       + FullyQualifiedErrorId : MethodCountCouldNotFindBest

  • I replace "$scrp.Script($db.Tables)" with

    "

    foreach($table in $db.Tables) {

     $scrp.Script($table)

    }

    ".

    This works for me.

Page 1 of 1 (2 items)