Powershell to Automate Reindexing on Azure SQL DB

I was working on Azure SQL DB and got the task of running reindexing over several Azure SQL DB… I thought of why not automating it instead of running the script over the Azure SQL dbs one by one …

Import-Module SQLPS
cls

  $ServerInstance = 'xyz.database.windows.net' # <============ Insert your Server Name

  $Database = 'master' 

  $UserName = 'Username' # <============ Insert your Password

  $Password = 'Password' # <============ Insert your User Name

  
  #$query1 is to get the Azure SQL databases list on which we have to run the TSQL , we can filter this by manipulating where clause to get the desired databases only.. It can be that you want to run the TSQL in only 4 databases out of 10... 
  $Query1 = @"

select * from sys.databases where name in ('Test'

)
"@  #here it is fetching all SQL Databases from master , we can filter however we like


#In $TargetTenants it will store the AZURE SQL databases name in an Array on which it has to run your script
$TargetTenants = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -UserName $UserName -Password $Password -Query $Query1

  

  

  #Query2 is the main query which is to be run on all databases... it can be any query - reindexing or login\user creation etc... 
  $Query2 = @"
  
declare @sql varchar(max)

DECLARE @INDEXcommand TABLE
(SQLCommand varchar(max) )

INSERT INTO @INDEXcommand
select   'ALTER INDEX '+t.name+' ON '+t.table_name+' REBUILD WITH (ONLINE = ON);'
from (
SELECT 
OBJECT_NAME(ips.OBJECT_ID) table_name
,i.NAME
,ips.index_id
,index_type_desc
,avg_fragmentation_in_percent
,avg_page_space_used_in_percent
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
where avg_fragmentation_in_percent>30 --and page_count > 1000
) t
ORDER BY avg_fragmentation_in_percent DESC

select * from @INDEXcommand


"@ # <============ Insert your command between the brackets


#Set a foreach loop for each of the Target Tenants(AZURE SQL DBs):


  $TargetTenants | ForEach-Object{

  $TargetTenant = $_.name

  [Array]$Result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $TargetTenant -UserName $UserName -Password $Password -Query $Query2 -Verbose 
  
  
  [Array]$INDEX

  Foreach ($INDEX in $Result)

 

  {

    write-host("Executing Command----  " + $INDEX[0]) -ForegroundColor Yellow
    $execute = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $TargetTenant -UserName $UserName -Password $Password -Query $INDEX[0] -Verbose 
    write-host("Completed Reindexing ") -ForegroundColor Green
   
  }



  }