How to script OPTIMIZE_FOR_SEQUENTIAL_KEY using smo?'

Question

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?

Answer 1

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

Answer 2

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."