How to script OPTIMIZE_FOR_SEQUENTIAL_KEY using smo?'

Bob 1971 1 Reputation point
2021-03-17T14:36:07.593+00:00

Hi.

I am on SQL Server 2019 / DB Edition 150.

When scripting via SSMS I get tables out with OPTIMIZE_FOR_SEQUENTIAL_KEY:

CREATE TABLE ...
CONSTRAINT ... PRIMARY KEY CLUSTERED 
(
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, **OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF**) ON [PRIMARY]
)

But when I use smo (powershell) I am unable to get this exact setting out when scripting the same tables. It tried to explicitly set the server version but it does not work:

$scripter.Options.TargetDatabaseEngineEdition = 2 #Standard
$scripter.Options.TargetDatabaseEngineType = 1    #Standalone
$scripter.Options.TargetServerVersion = 9         #Version150

Any ideas on how to get it via the Scripter object?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,333 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,456 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,598 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Paul Staniforth 76 Reputation points
    2021-03-17T15:27:52.76+00:00

    Hi,

    You will need to define the Scripting Options to determine what is included in the script output.

    For example to include all the table constraints you will need to set DRIALL to true. The default option is false

    Please see the Microsoft documentation for a list of all the available script options: -

    https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.scriptingoptions?view=sql-smo-160#:~:text=Constructors%20%20%20Scripting%20Options%20%28%29%20%20,%20Constructs%20a%20ScriptingOptions%20object%20usi%20...%20

    There is an option to include OptimzerData which you may need to include as well.

    If you require further assistance please provide a full list of the Scripting Options you are defining

    Paul

    0 comments No comments

  2. Bob 1971 1 Reputation point
    2021-03-18T13:19:57.427+00:00

    Hi Paul.

    Thanks for the fast reply and the suggestion! However the OPTIMIZER... option is still not being scripted.

    The code is below, maybe it gives some hint to what I am missing?!

    BR Bob

    $ServerName='.'# the server it is on
    $Database='DB' # the name of the database you want to script as objects
    $DirectoryToSaveTo='C:\temp\' # 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.Options.TargetDatabaseEngineEdition = 2 #Standard
    $scripter.Options.TargetDatabaseEngineType = 1    #Standalone
    $scripter.Options.TargetServerVersion = 9         #Version150
    $scripter.Options.IncludeDatabaseContext = $true # true = USE <databasename> statements
    $scripter.Options.IncludeDatabaseRoleMemberships = $false
    $scripter.Options.ToFileOnly = $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 includede
    $scripter.Options.Permissions= $true # This should be includede
    $scripter.Options.OptimizerData= $true # This should be includede
    
    # first we get the bitmap of all the object types we want
    $objectsToDo =[long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::All -bxor (
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Certificate +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseScopedConfiguration + 
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::MessageType +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceBroker +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceContract +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceQueue +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceRoute )
    
    # and we store them in a datatable
    $d = new-object System.Data.Datatable
    # get just the tables
    $d=$srv.databases[$Database].EnumObjects($objectsToDo) |
        Where-Object {
        $_.Schema -ne "sys" -and
        $_.Name -ne "sys" -and
        $_.Schema -ne "information_schema" -and
        $_.Name -ne "information_schema" -and
        $_.Schema -notlike "db_*" -and
        $_.Name -notlike "db_*" -and
        $_.Name -notlike "Microsoft*" -and
        $_.Name -notlike "sp_*diagram*" -and
        $_.Name -ne "fn_diagramobjects" -and
        $_.Name -ne "sysdiagrams" -and
        $_.Schema -ne "guest" -and
        $_.Name -ne "guest" }
    
    # 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
        $scripter.script($URNCollection)
        }
    "Done."
    
    0 comments No comments