PowerShell script to list all Azure SQL Databases in a Azure Subscription

az_getSQLDatabaseListInExcel.ps1

PARAM(
    [string] [Parameter(Mandatory = $True, HelpMessage = "Choose subscription you want be inventored")] $SubscriptionName
    )

#Variables
$ReportDate = (Get-Date).ToString("yyyy-MM-dd HH:mm")


#Login to Azure
Connect-AzAccount
 
#Select Azure Subscription
Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription
 
#Collect Data
$AzureSQLBackupInventory = @()
$AzureSQLServers = Get-AzResource  | Where-Object ResourceType -EQ Microsoft.SQL/servers
foreach ($AzureSQLServer in $AzureSQLServers){
    $AzureSQLServerDataBases = Get-AzSqlDatabase -ServerName $AzureSQLServer.Name -ResourceGroupName $AzureSQLServer.ResourceGroupName | Where-Object DatabaseName -NE "master"
        foreach ($AzureSQLServerDataBase in $AzureSQLServerDataBases) {
            $DBLevelInventory =  @()
            $BackupState = Get-AzSqlDatabaseGeoBackupPolicy  -ServerName $($AzureSQLServerDataBase.ServerName) -DatabaseName $($AzureSQLServerDataBase.DatabaseName) -ResourceGroupName $($AzureSQLServerDataBase.ResourceGroupName) | Select-Object -ExpandProperty State

            $DBLevelInventory = New-Object -TypeName psobject
                $DBLevelInventory | Add-Member -MemberType NoteProperty -Name "Subscription Name" -Value $SubscriptionName
                $DBLevelInventory | Add-Member -MemberType NoteProperty -Name "Resource Group" -Value $AzureSQLServerDataBase.ResourceGroupName
                $DBLevelInventory | Add-Member -MemberType NoteProperty -Name "SQL Server Name" -Value $AzureSQLServerDataBase.ServerName
                $DBLevelInventory | Add-Member -MemberType NoteProperty -Name "DataBase Name" -Value $AzureSQLServerDataBase.DatabaseName
                $DBLevelInventory | Add-Member -MemberType NoteProperty -Name "Creation Date" -Value $AzureSQLServerDataBase.CreationDate 
                $AzureSQLBackupInventory+=$DBLevelInventory
                        
        }
}

#region
#--------------------------------------------------------------------------
# Build File and Output to Path
$dateString = $startDate.ToString("yyyyMMdd")
$filePath = "C:\temp\"
$fileName = ("Azure-SQL_Report_" + $SubscriptionName + "_" + $dateString + ".html")
$outFile = $filePath + $fileName
#--------------------------------------------------------------------------
#endregion

$AzureSQLBackupInventory | Export-Excel C:\Temp\azure_sql_databases.xls