dbatools (或SQL Server的SMO )-模式导出期间的表排序

Question

我正在编写一个dbatools脚本，用于从数据库模式中导出各种项目。使用Export-DbaScript时，表、索引、PK/FK会按预期转储。

然而，表的顺序和它的约束被以错误的顺序转储。例如，使用FK约束将表Foo转储到一个直到脚本中稍后才出现的表。这会导致转储在执行时无用。

请注意，这可能也适用于SMO API，因为据我所知，dbatools基本上是一个包装器。我还试着摆弄各种ScriptingOptions，但没有成功。

伪脚本示例：

$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

输出示例，其中BAR在FK_FOO_BAR约束之后创建：

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]

有一件事我还没有尝试过，那就是遍历表集合，为每个表调用.Script()，然后再次运行，最后为约束调用.Script()。但我希望SMO / dbatools能处理这个问题。

Answer 1

回答我自己的问题:我假设Export-DbaScript根据configuration对象来处理这个问题。事实并非如此，因此导出必须在两次迭代中完成:首先是表对象，然后是FK。

示例代码：

# 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