Write-DbaDbTableData AutoCreate Should Create Schema

[serenefiresiren]

Summarize Functionality

The New-Table function does not create a missing schema when AutoCreateTable is used. I believe this should match the functionality in the existing New-DbaDBTable which automatically creates a missing schema even if not the same way.

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

Error Message:

WARNING: [12:19:30][Write-DbaDbTableData] Failed to create table [MyDB].[Test].[NewTable] | The specified schema n
ame "Test" either does not exist or you do not have permission to use it.

Snippet from New-DbaDbTable line 464 for existing example.

# user has specified a schema that does not exist yet
                    $schemaObject = $null
                    if (-not ($db | Get-DbaDbSchema -Schema $Schema -IncludeSystemSchemas)) {
                        Write-Message -Level Verbose -Message "Schema $Schema does not exist in $db and will be created."
                        $schemaObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Schema $db, $Schema
                    }

The schema is already being parsed out at line 430, which appears to be done before #region Test if table exists is reached.

        if ($fqtnObj.Schema) {
            $schemaName = $fqtnObj.Schema
        } else {
            $schemaName = $Schema
        }

I believe the check should be put within #region Test if table exists that starts at 506. A rough guess at how it may look.

  } else {
            # We don't use SMO here because it does not work for Azure SQL Database connected with AccessToken.

           if (-not ($DatabaseName | Get-DbaDbSchema -Schema $schemaName -IncludeSystemSchemas)) {
                        Write-Message -Level Verbose -Message "Schema $Schema does not exist in $db and will be created."
                      = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Schema $schemaName , 
 
                              $schemaObject  | Invoke-Create  
            }
            try {
                $null = $server.ConnectionContext.ExecuteScalar("SELECT TOP(1) 1 FROM $fqtn")
                $tableExists = $true
            } catch {
                $tableExists = $false
            }
        }

[mattcargile]

I could see a -Force being implemented somewhere in here; though -AutoCreateTable would insinuate a forcing of the schema creation. I could also see having -AutoCreateSchema on Write-DbaDbTableData to be extra -Verbose.

Additionally, I would expect New-DbaDbTable to fail if the schema doesn't exist and have a -Force parameter or -AutoCreateSchema.