Reworked version of Pieter Vanhove's Azure SQL Database reindexing Azure Automation Runbook

SQLServerIndexMaintenance.ps1

<#
.SYNOPSIS
 Perform index maintenance
 
.DESCRIPTION
 This runbook uses Azure Automation to perform reindexing and statistics maintenance of all databases on a target server.
 As prerequisite, please create an Azure Automation credential asset that contains the username and password for the target Azure SQL DB logical server ($SqlServerName).
 Make sure that you have installed the scripts IndexOptimize.sql and CommandExecute.sql of Ola Hallengren (https://ola.hallengren.com/downloads.html)
 Make sure to get Ola's modified scripts which work on Azure here: https://ola.hallengren.com/downloads.html
  
.EXAMPLE
 SQLServerIndexMaintenance
  
.NOTES
 AUTHOR: Original author Pieter Vanhove: http://pietervanhove.azurewebsites.net/?p=14137
         Heavily modified by Larry Silverman, CTO, TrackAbout
#>
 
workflow SQLServerIndexMaintenance
{
    param (
        # Fully-qualified name of the Azure DB server 
        [parameter(Mandatory=$true)] 
        [string] $SqlServerName,

        # Credentials for $SqlServerName stored as an Azure Automation credential asset
        # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
        [parameter(Mandatory=$true)] 
        [PSCredential] $Credential
    )
  
    inlinescript {
        # Set up credentials 
        $ServerName = $Using:SqlServerName
        $UserId = $Using:Credential.UserName
        $Password = ($Using:Credential).GetNetworkCredential().Password
        $databases = @()

        # Create connection to Master DB
        Try {
            $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
            $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;"
            $MasterDatabaseConnection.Open();

            # Create command to query the name of active databases in $ServerName
            $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
            $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
            $MasterDatabaseCommand.CommandText = 
            "
            select name from sys.databases
            where state_desc='ONLINE'
            and name <> 'master'
            "
            
            $MasterDbResult = $MasterDatabaseCommand.ExecuteReader()
            
            while($MasterDbResult.Read()) {
                $databases += @($MasterDbResult[0].ToString())
            }
        }
        # Catch errors connecting to master database.
        Catch {
            Write-Error $_
        }
        Finally {
            if ($null -ne $MasterDatabaseConnection) {
                $MasterDatabaseConnection.Close()
                $MasterDatabaseConnection.Dispose()
            }
        }

        # Create connection for each individual database
        # Iterate through each database under $ServerName
        foreach ($DbName in $databases) {
            Try {
                # Setup connection string for $DbName
                $ChildDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
                $ChildDatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"
                $ChildDatabaseConnection.Open();

                # Create command for a specific database $DBName
                $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
                $DatabaseCommand.Connection = $ChildDatabaseConnection

                Write-Output "Performing index and statistics maintenance on $DbName"

                # ExampleTable is a place holder for a table that holds a large volume of less important and expendable data
                # that can be truncated to save space on the database.

                $DatabaseCommand.CommandText ="
                EXECUTE dbo.IndexOptimize
                @Databases = '" + $DbName + "',
                @FragmentationLow = NULL,
                @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
                @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
                @FragmentationLevel1 = 5,
                @FragmentationLevel2 = 30,
                @UpdateStatistics = 'ALL',
                @OnlyModifiedStatistics = 'Y'
                "
                $DatabaseCommand.CommandTimeout = 0
                #Write-Output $DatabaseCommand.CommandText
                $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()
            }

            # Inner catch for individual database failures.
            # We want to keep processing the next database.
            Catch {
                Write-Error $_
            }

            Finally {
                if ($null -ne $ChildDatabaseConnection)
                {
                    $ChildDatabaseConnection.Close()
                    $ChildDatabaseConnection.Dispose()
                }
            }
        }
    }
}