Hi @Simon Holland Thank you for posting you question on Microsoft Q&A and for using Azure services.
As per the description above, you are trying to create an automation to rebuild database indexes using elastic jobs.
Elastic jobs can target all databases in a server or Elastic pool. Moreover, it does dynamic enumeration of databases at run time, an important consideration for SaaS customers who keep adding/dropping databases when new customers are added/dropped. Job scripts will enumerate the list of DBs at the run time and will pick them up automatically without having to change the script. That facilitates the automation by not having to change the script when new customers/DBs are added/dropped from a server/pool. In addition, you can also specify exclude list to exclude individual databases in a server/pool. Azure Documentation has more details here.
- if you want to execute Invoke-SqlCmd on all the DBs in a pool, without having to individually write separate lines of code per DB, one possible way is to use the PowerShell Get-AzSqlDatabase cmdlet and then loop through the DBs within that pool. Further, you could exclude DBs by providing an array of such DB names to exclude:
Get-AzSqlDatabase -ResourceGroupName $VARIABLE_FOR_RESOURCE_GROUP_NAME -ServerName $AzureSQLServerName | Where-Object ElasticPoolName -eq $VARIABLE_FOR_ELASTIC_POOL_NAME | Where-Object DatabaseName -NotIn @("DB_NAME_TO_EXCLUDE", "DB_NAME_TO_EXCLUDE") | % { Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database ($_.DatabaseName) -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 }
All the Elastic Job T-SQL APIs also have corresponding Powershell and REST APIs that can be used for creating and executing jobs, including ability to target all DBS in a server/pool and also selectively exclude certain DBs if needed. Some examples of Power Shell APIs can be found in the documentation also.
Hope that helps
Regards,
Oury