Run a command For Each Azure SQL database with PowerShell

Updated: Jan 8

Azure SQL databases don't behave like on-premise SQL Server databases and unfortunately, we can't use the handy (and undocumented) sp_MSforeachdb stored procedure to run a SQL command on each database. To work this issue around, I am sharing with you a simple piece of PowerShell code to do that easily:

#Set Parmeters:


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

$Database = 'master'

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

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

$Query = 'SELECT name FROM sys.databases'

$TargetTenants = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -UserName $UserName -Password $Password -Query $Query


$Query = '' # <============ Insert your command between the brackets


#Set a foreach loop for each of the Target Tenants:


$TargetTenants | ForEach-Object{

$TargetTenant = $_.name

Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $TargetTenant -UserName $UserName -Password $Password -Query $Query -Verbose

}


But what if your SQL command contains dynamic SQL??

In that case, since you might struggle with handling the brackets, I recommend using an "InputFile" parameter to call a saved .sql file instead of writing or copying the code itself.


Here's how you do it:


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

$Database = 'master'

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

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

$Query = 'SELECT name FROM sys.databases'

$TargetTenants = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -UserName $UserName -Password $Password -Query $Query


$InputFile = '' # <============ Insert your file path between the brackets


#Set a foreach loop for each of the Target Tenants:


$TargetTenants | ForEach-Object{

$TargetTenant = $_.name

Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $TargetTenant -UserName $UserName -Password $Password -InputFile $InputFile -Verbose

}