Export von Tabellendaten per PowerShell und SQL

<#
    This script is provided "as is" and may be freely used, modified, and distributed.
    However, no warranty is given, either express or implied, including but not limited to
    warranties of merchantability or fitness for a particular purpose.
    Use at your own risk!
#>

# Define server/database
$Server = "<Server>"
$DatabaseName = "<DateBase>"

$ResultFileName = "Results.csv"

$TableName = 'Change Log Entry$437dbf0e-84ff-417a-965d-ed2bb9650972'
# Use this query to export all columns/rows
$BaseQuery = "SELECT * FROM"
# Use this query to count rows
# $BaseQuery = "SELECT Count(timestamp) AS Count FROM"

[switch]$UseExtensionTable
[switch]$DataPerCompany = $true
$TableString = if($UseExtensionTable){
   "[$TableName] NATURAL JOIN [$TableName`$ext]"
}
else {
   "[$TableName]"
}


$results = @()

# Update outer progress
Write-Progress -Id 1 `
    -Activity "Processing database" `
    -Status "$Server\$DatabaseName"

try {
    $Query = "$BaseQuery [$DatabaseName].[dbo].$TableString"
    if($DataPerCompany){
        Write-Host "Getting Companies"
        # Determine if the table is global or per company
        # Get First CompanyName
        $QueryString = "SELECT Name FROM Company;"
        $result = Invoke-Sqlcmd -ServerInstance $Server -Database $DatabaseName -Query $QueryString
        $Companies = $result.Name
        $QueryString = ""
        $i = 0
        foreach($Company in $Companies){
            $i++
            Write-Progress -Id 2 `
                -Activity "Processing companies" `
                -Status "[$i] $Company"
            
            $SafeCompanyName = $Company.Replace("/","_").Replace(".","_")
            $QueryString = $Query.Replace($TableName, ("$SafeCompanyName`$$TableName"))

            Write-Host "Executing query on $server\$DatabaseName..." -ForegroundColor Cyan
            Write-Host $QueryString
            $result = Invoke-Sqlcmd -ServerInstance $server -Database $DatabaseName -Query $QueryString
            Write-Host "Result from $server\$DatabaseName" -ForegroundColor Green
            $result | Add-Member -MemberType NoteProperty -Name "Database" -Value $DatabaseName
            $result | Add-Member -MemberType NoteProperty -Name "Server" -Value $Server
            $result | Add-Member -MemberType NoteProperty -Name "Company" -Value $Company
            $results += $result
        }
        Write-Progress -Id 2 -Activity "Processing companies" -Completed
    }
    else {
        $QueryString = $Query

        Write-Host "Executing query on $server\$DatabaseName..." -ForegroundColor Cyan
        Write-Host $QueryString
        $result = Invoke-Sqlcmd -ServerInstance $server -Database $DatabaseName -Query $QueryString
        Write-Host "Result from $server\$DatabaseName" -ForegroundColor Green
        $result | Add-Member -MemberType NoteProperty -Name "Database" -Value $DatabaseName
        $result | Add-Member -MemberType NoteProperty -Name "Server" -Value $Server
        $results += $result        
    }
} catch {
    Write-Warning "Failed to execute query on $server\$DatabaseName`: $_"
}

Write-Progress -Id 1 -Activity "Processing databases" -Completed

Write-Host "Exporting results to $ResultFileName..." -ForegroundColor Cyan
$results | Export-Csv $ResultFileName -Delimiter ";" -NoTypeInformation
write-Host "Results exported" -ForegroundColor Green