SMO scripting doesn't generate Create Statistics

This topic contains 1 reply, has 1 voice, and was last updated by Profile photo of Randy Petty Randy Petty 5 months, 3 weeks ago.

  • Author
    Posts
  • #54968
    Profile photo of Randy Petty
    Randy Petty
    Participant

    Something is inconsistent about this powershell scripting with SMO.( sql server ) With these options I get create statistic statements on about 12 tables where there are numerous statistics on many other tables that powershell left out. The create index statements should re-create index-related statistics, but the vast majority of column-based stats are left out: e.g. _WA_Sys_00000002_69FBBC1F

    $scripter = new-object ("$My.Scripter") $srv # create the scripter
    #$scripter.DiscoverDependencies(
    $scripter.Options.ToFileOnly = $true
    #$scripter.Options.WithDependencies =$true
    $scripter.Options.Statistics=$true
    $scripter.Options.ExtendedProperties= $true # yes, we want these
    $scripter.Options.DRIAll= $true # and all the constraints
    $scripter.Options.Indexes= $true # Yup, these would be nice
    $scripter.Options.Triggers= $true # This should be included
    $scripter.Options.DriForeignKeys=$true

    The full script:

    $ServerName='sacsqldevvs001.cm.fdielt.com\sacsqldevinst001' # the server it is on
    $Database='cmsperformance' # the name of the database you want to script as objects
    $DirectoryToSaveTo='C:\Users\randall.petty\Documents\work\powershell' # the directory where you want to store them
    # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
    $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
    if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
       [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
    }
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null
     
    set-psdebug -strict # catch a few extra bugs
    $ErrorActionPreference = "stop"
    $My='Microsoft.SqlServer.Management.Smo'
    $srv = new-object ("$My.Server") $ServerName # attach to the server
    if ($srv.ServerType-eq $null) # if it managed to find a server
       {
       Write-Error "Sorry, but I couldn't find Server '$ServerName' "
       return
    }
    $scripter = new-object ("$My.Scripter") $srv # create the scripter
    #$scripter.DiscoverDependencies(
    $scripter.Options.ToFileOnly = $true
    #$scripter.Options.WithDependencies =$true
    $scripter.Options.Statistics=$true
    $scripter.Options.ExtendedProperties= $true # yes, we want these
    $scripter.Options.DRIAll= $true # and all the constraints
    $scripter.Options.Indexes= $true # Yup, these would be nice
    $scripter.Options.Triggers= $true # This should be included
    $scripter.Options.DriForeignKeys=$true
    
    # first we get the bitmap of all the object types we want
    $objectsToDo =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Table
    # and we store them in a datatable
    $d = new-object System.Data.Datatable
    
    
    # get just the tables
    $d=$srv.databases[$Database].EnumObjects($objectsToDo) 
    # and write out each scriptable object as a file in the directory you specify
    $d| FOREACH-OBJECT { # for every object we have in the datatable.    
        $SavePath="$($DirectoryToSaveTo)\$($_.DatabaseObjectTypes)\$($_.Schema)"
        # create the directory if necessary (SMO doesn't).
        if (!( Test-Path -path $SavePath )) # create it if not existing
               {Try { New-Item $SavePath -type directory | out-null }
            Catch [system.exception]{
                 Write-Error "error while creating '$SavePath' $_"
                 return
                 }
            }
        # tell the scripter object where to write it
        $scripter.Options.Filename = "$SavePath\$($_.name -replace '[\\\/\:\.]','-').sql";
        # Create a single element URN array
        $UrnCollection = new-object ("$My.urnCollection")
        $URNCollection.add($_.urn)
        # and write out the object to the specified file
        #Write-Host $_.urn
        #if ( $_.urn -match 'Persistent_Object_Reference') {
        $scripter.script($URNCollection)
       # }
        }
        
    "All is written out, wondrous human"
  • #55001
    Profile photo of Randy Petty
    Randy Petty
    Participant

    Guess we're all stumped.

You must be logged in to reply to this topic.