Export every table in the AdventureWorksDW2017 db to Excel

AdventureWorksDW2017_ExportExcel.ps1

#Requires -Modules SqlServer 
#Requires -Modules ImportExcel 
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#>
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables

<# Scenario #1 A) all Dimensions in a single file,
    and B) each Fact table in their own file. #>
 
<# A) Every Dimension table in a worksheet named after the table, the same Excel file #>
dir | WHERE { $_.name -like 'dim*' } |
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017_Dims.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors 
}

<# B) Each Fact-table in it's own Excel file, named after the table. #>
dir | WHERE { $_.name -like 'fact*' } |
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count
}


<# Scenario #2 Each table in it's own Excel file, named after the table. #> 
dir | 
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count
}
 
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables
<# Scenario #3 Every table in a worksheet named after the table, all in the same Excel file #>
dir | 
foreach {
"$($_.Name)"
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors 
}