THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Ben Miller

Table scripting with SMO (Part 2 with Powershell)

In my previous post I illustrated scripting tables out with SMO in .NET.  I have tested a new version this time in Powershell.  I used the model with args, but you can just put it in the script as well.  This also goes and scripts out your Views as well, but only the ones that are non-system objects, because if you take off the WHERE in the bottom, you will get all the sys.* and INFORMATION_SCHEMA views as well.

# scripttables.ps1
# Lists space used by tables in database of your choice
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$server = $args[0]
$dbname = $args[1]
$output = "c:\temp\output.txt"

$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$scr = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter")

$db = $srv.Databases[$dbname]

$scr.Server = $srv
$options = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")
$options.DriAll = $True
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.IncludeHeaders = $true
$options.AppendToFile = $false
$options.FileName = $output
$options.ToFileOnly = $true

$scr.Options = $options
$scr.Script($db.Tables)

$options.AppendToFile = $true
$views = $db.Views | where {$_.IsSystemObject -eq $false}
$scr.Script($views)

 

Published Monday, October 15, 2007 9:32 AM by dbaduck

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Amil said:

Should you also include and save the $db.StoredProcedures which I think can be done exactly like the views.

August 4, 2008 7:01 PM
 

Adam Justyn said:

I run the script scripttables.ps1 and got the errors:

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

At C:\CheckSQLServer\scriptingtables.ps1:28 char:12

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

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

   + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Exception calling "Script" with "1" argument(s): "Script failed for Server 'hyp

erion'. "

At C:\CheckSQLServer\scriptingtables.ps1:32 char:12

+ $scr.Script <<<< ($views)

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

   + FullyQualifiedErrorId : DotNetMethodException

Do you have any idea?

Regards

Adam

March 2, 2010 7:56 AM
 

David said:

Seems to be a bug with Windows 7 RTM Powershell V2.  Same script runs correctly on Windows 2003 R2.

April 20, 2010 7:21 AM
 

DB said:

I got this through some other web site stating to use UrnCollection object and it worked, i can post full script if you request it...

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection

foreach($item in $db.StoredProcedures  | where {!($_.IsSystemObject) } )

    {

      $scrp.Options.AppendToFile = $False

      $scrp.Options.DriAll = $True

      $scrp.Options.ScriptDrops = $False  

      $scrp.Options.IncludeHeaders = $True

      $scrp.Options.ToFileOnly = $True

      $scrp.Options.WithDependencies = $False

        $scrp.Options.FileName = $SP + $item + "_" + [DateTime]::Today.ToString("yyyyMMddHHmm") + ".sql"

           $smoObjects.Add($item.Urn)

           $scrp.Script($smoObjects)

           $smoObjects.Remove($item.Urn)

      }

   }

April 1, 2013 6:15 PM

Leave a Comment

(required) 
(required) 
Submit

About dbaduck

Ben Miller is a Senior Database Administrator for HealthEquity in Draper, UT. He has been working with SQL since SQL Server 6.0 (1998) and has had a variety of roles in his career, including SQL Support and MVP Lead at Microsoft.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement