tcdbtools

1.0.0

functions/private/Invoke-DBScriptObjects-functions.ps1

################################################################################
# Function Script Out Database Object
#
# Function that accepts a Smo.Database-related object or an object with a valid
# Urn property, then calls the already instantiated Scripter object to write
# sql script to file.
#
# Args: $dbObj = Database object from either a "list" (generated by EnumObjects
# method) or a database object with a Urn property (i.e. Table,
# StoredProcedure, etc)
################################################################################
function ScriptOutDbObj($scripter, $dbObj, $SavePath) {
    # Create a single element URN array
    $UrnCollection = New-Object ("Microsoft.SqlServer.Management.Smo.UrnCollection")
    $UrnCollection.Add($dbObj.Urn)

    # get the valid Urn.Type string for the file name
    $typeName = "$($UrnCollection.Item(0).Type)s"

    if ($typeName -ieq "Databases") {
        $typeName = "Database"
    }

    $ignore = $null
    # a regex variable be defined external to this script to allow ignoring types with matching names. EX: $ignoreSchemas = ".*?_"
    $ignore = Get-Variable -Name "ignore$typeName" -ValueOnly -ErrorAction SilentlyContinue

    if ($ignore -and $dbObj.Name -imatch $ignore) {
        Write-Verbose "IGNORING: $typeName - $($dbObj.Name)"
        return;
    }

    $tempPath = Resolve-Path $SavePath;
    if ("$($dbObj.Schema)".Length -gt 0) {
        $safeName = "$($dbObj.Schema).$($dbObj.Name).sql"
    } else {
        $safeName = "$($dbObj.Name).sql"
    }
    $tempPath = [IO.Path]::Combine($SavePath, $dbObj.Schema, $typeName, $safeName.Split([IO.Path]::GetInvalidFileNameChars()) -join '_')

    $dir = [IO.Path]::GetDirectoryName($tempPath)

    if (!(Test-Path $dir -PathType Container)) {
        New-Item $dir -ItemType Directory | Out-Null
    }

    ## tell the scripter object where to write it
    $scripter.Options.Filename = $tempPath

    # a bit of progress reporting...
    Write-Verbose $scripter.Options.FileName

    try {
        #and write out the object to the specified file
        $scripter.Script($UrnCollection)
    } catch {
        Write-Warning "ERROR SCRIPTING ($($typeName)): $tempPath, EXCEPTION: $($_.Exception.GetBaseException().Message)"
    }

    # some sys items wont script out except as empty files, this is the easiest way to get rid of them
    if ((Get-Item $scripter.Options.Filename).length -le 0kb) {
        Remove-Item $scripter.Options.Filename -Verbose:$VerbosePreference
    }
}
# End of Function ScriptOutDbObj
################################################################################