[SOLVED] dbatools (or, SMO for SQL Server) - table ordering during schema export

dbatools (or, SMO for SQL Server) - table ordering during schema export

I am writing a dbatools script, for exporting various items from the database schema. When using Export-DbaScript, the tables, indexes, PK/FK are dumped as expected.

However, the ordering of the tables and it's constraints are dumped in the wrong order. For example, table Foo are dumped with FK constraints to a table that doesn't appear until later in the script. This causes the dump to be useless for execution.

Note that this probably applies to the SMO API as well, as dbatools is basically a wrapper as far as I know. I also tried to fiddle with the various ScriptingOptions, without luck.

Example pseudo script:

$schemaTables = Get-DbaDbTable -SqlInstance $serverInstance -Database $database -Schema $schema
# Set options, and dump to file
$options = New-DbaScriptingOption
$options.ContinueScriptingOnError = $false
$options.DriAllConstraints =$true
$schemaTables | Export-DbaScript -FilePath $schemaFile -ScriptingOptionsObject $options -EnableException

Example output, where BAR is created after the FK_FOO_BAR constraint:

CREATE TABLE [acme].[FOO](
    [ID] [uniqueidentifier] NOT NULL,
    [dateFrom] [datetime2](7) NOT NULL,
    [dateTo] [datetime2](7) NULL,
    --- ...and so forth
 CONSTRAINT [R161_pk] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [acme].[FOO]  WITH CHECK ADD  CONSTRAINT [FK_FOO_BAR] FOREIGN KEY([ID])
REFERENCES [acme].[BAR] ([fooID])
ALTER TABLE [acme].[FOO] CHECK CONSTRAINT [FK_FOO_BAR]

---- BAR created after FK_FOO_BAR, stuff breaks

CREATE TABLE [acme].[BAR](
    [fooID] [uniqueidentifier] NOT NULL,
    [teamName] [nvarchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
    --- ...and so forth
 CONSTRAINT [PK_PTRLICENCE] PRIMARY KEY CLUSTERED 
(
    [fooID] ASC
)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]
) ON [PRIMARY]

One thing I haven't tried yet, is to iterate over the table collections, and call .Script() for each table, then run again and to .Script() for the constraints in the end.. but I was hoping SMO / dbatools could handle this.

Solution

Answering my own question: I acted on the assumption that Export-DbaScript handles this based on the configuration object. That's not the case, so the export has to be done in two iterations: Table objects first, then the FK.

Sample code:

# Export tables plainly without FK etc
$options = New-DbaScriptingOption
$options.ContinueScriptingOnError = $false
$options.DriIndexes = $true
$options.SpatialIndexes = $true
$options.DriPrimaryKey = $true
$tablesAll | Export-DbaScript -FilePath $schemaFile -ScriptingOptionsObject $options -EnableException -NoPrefix

# Export table foreign keys, triggers etc (must be done after table definitions)  
$options = New-DbaScriptingOption
$options.ContinueScriptingOnError = $false
$options.PrimaryObject = $false
$options.DriForeignKeys = $true
$options.Triggers = $true;
$tablesAll | Export-DbaScript -FilePath $schemaFile -ScriptingOptionsObject $options -EnableException -Append -NoPrefix