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
}
I hope you find this script helpful!
Comentarios