workflow Runbook-UPDATESTATS
{
    param(
        [parameter(Mandatory=$True)]
        [string] $SqlServer,

        [parameter(Mandatory=$True)]
        [string] $Database,

        [parameter(Mandatory=$True)]
        [string] $SQLCredentialName


    )

    # Get the stored username and password from the Automation credential
    $SqlCredential = Get-AutomationPSCredential -Name $SQLCredentialName
    if ($SqlCredential -eq $null)
    {
        throw "Could not retrieve '$SQLCredentialName' credential asset. Check that you created this first in the Automation service."
    }

    $SqlUsername = $SqlCredential.UserName
    $SqlPass = $SqlCredential.GetNetworkCredential().Password

    $TableNames = Inlinescript {

        # Define the connection to the SQL Database
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

        # Open the SQL connection
        $Conn.Open()

        # Return the tables
        $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
        $Cmd.CommandTimeout=120

        # Get the list of tables with their object ids
        $SQLCommandString = @"
        SELECT '['+s.name +'].[' + t.name + ']' AS TableName
        FROM sys.tables t
        join sys.schemas s on t.schema_id = s.schema_id
"@

        $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
        $Cmd.CommandTimeout=120

        # Execute the SQL command
        $TableSchema =New-Object system.Data.DataSet
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
        [void]$Da.fill($TableSchema)

        $Conn.Close()
    }

    # Interate through tables with high fragmentation and rebuild indexes
    ForEach ($TableName in $TableNames)
    {
      Write-Verbose "Creating checkpoint"
      Checkpoint-Workflow
      Write-Verbose "Updating Table $TableName..."

      InlineScript {

        $SQLCommandString = @"
        EXEC('UPDATE STATISTICS  $Using:TableName WITH FULLSCAN')
"@

        # Define the connection to the SQL Database
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

        # Open the SQL connection
        $Conn.Open()

        # Define the SQL command to run. In this case we are getting the number of rows in the table
        $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
        # Set the Timeout to be less than 30 minutes since the job will get queued if > 30
        # Setting to 25 minutes to be safe.
        $Cmd.CommandTimeout=1500

        # Close the SQL connection
        $Conn.Close()
      }
    }

    Write-Verbose "Finished Indexing"
}