Query multiple Azure SQL Databases

I was sick of changing context in Azure Data Studio while cleaning up things discovered in our vulnerability assessments.

Changing things via PowerShell

I wanted to be cautious with drops across multiple subscriptions, so I started with a single subscription. I use Ghostty for my command line stuff. Get into a PowerShell prompt, and execute Connect-AzAccount.

This prompts me to log in with a browser, and then in Ghostty, I can choose which subscription I want to run this script in. I had some leftover users we don’t need anymore, so that’s what I’m dropping here.

# SQL to run on each DB
$sqlCommand = "DROP USER [DBDiagramUser];"

# Get access token for Azure SQL
function Get-AzSqlAccessToken {
    $tokenResponse = Get-AzAccessToken -ResourceUrl "https://database.windows.net/"
    return $tokenResponse.Token
}

# Loop through all servers and DBs
$servers = Get-AzSqlServer
foreach ($server in $servers) {
    $databases = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName

    foreach ($db in $databases) {
        # Skip system DBs
        if ($db.DatabaseName -in @("master", "tempdb", "model", "msdb")) {
            continue
        }

        $accessToken = Get-AzSqlAccessToken

        Write-Host "Running script on $($server.ServerName).database.windows.net / $($db.DatabaseName)"

        try {
            # Create SQL connection with token
            $connection = New-Object System.Data.SqlClient.SqlConnection
            $connection.ConnectionString = "Server=$($server.ServerName).database.windows.net;Database=$($db.DatabaseName);Encrypt=True;TrustServerCertificate=False;"
            $connection.AccessToken = $accessToken
            $connection.Open()

            $command = $connection.CreateCommand()
            $command.CommandText = $sqlCommand
            $command.ExecuteNonQuery()

            $connection.Close()
        }
        catch {
            Write-Warning "Failed on $($db.DatabaseName): $_"
        }
    }
}

Maybe I get more comfortable dropping across subscriptions, or I get bored of manually changing to different subscriptions, and I use this instead. I like to be very cautious about changing things wholesale, though.

# Get all subscriptions for the logged-in account
$subscriptions = Get-AzSubscription

# Define the SQL script to run (e.g., DROP USER)
$sqlScript = "DROP USER [DBDiagramUser];"

# Loop through each subscription
foreach ($subscription in $subscriptions) {
    # Set the context to the current subscription
    Set-AzContext -SubscriptionId $subscription.Id

    Write-Host "Processing Subscription: $($subscription.Name) ($($subscription.Id))"

    # Get all SQL Servers in the current subscription
    $servers = Get-AzSqlServer

    # Loop through each server and list databases
    foreach ($server in $servers) {
        Write-Host "  Server: $($server.ServerName)"

        # Get all databases on the current server
        $databases = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName

        foreach ($db in $databases) {
            # Skip system databases
            if ($db.DatabaseName -in @("master", "tempdb", "model", "msdb")) {
                continue
            }

            Write-Host "Running script on Database: $($db.DatabaseName)"

            try {
                # Get access token for Azure SQL
                $accessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

                # Create SQL connection with token
                $connection = New-Object System.Data.SqlClient.SqlConnection
                $connection.ConnectionString = "Server=$($server.ServerName).database.windows.net;Database=$($db.DatabaseName);Encrypt=True;TrustServerCertificate=False;"
                $connection.AccessToken = $accessToken
                $connection.Open()

                # Create and execute the SQL command
                $command = $connection.CreateCommand()
                $command.CommandText = $sqlScript
                $command.ExecuteNonQuery()

                # Close the connection
                $connection.Close()

                Write-Host "Successfully executed script on $($db.DatabaseName)"
            }
            catch {
                Write-Warning "Failed to execute script on $($db.DatabaseName): $_"
            }
        }
    }
}

Querying things via PowerShell

If you want to run SELECTs across multiple subscriptions and return the results, you have to change that PowerShell a bit so it will output the results for you. I feel much better about multiple subscriptions when I’m returning some results, but still be careful; you aren’t running crazy-intensive SELECTs.

# Get all subscriptions
$subscriptions = Get-AzSubscription

# Define query for top 5 largest tables by row count
$sqlScript = @"
SELECT TOP 5 
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS TotalRows
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.index_id <= 1
GROUP BY s.name, t.name
ORDER BY TotalRows DESC;
"@

# Initialize result collection
$results = @()

# Loop through all subscriptions
foreach ($subscription in $subscriptions) {
    Set-AzContext -SubscriptionId $subscription.Id

    $servers = Get-AzSqlServer

    foreach ($server in $servers) {
        $databases = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName

        foreach ($db in $databases) {
            if ($db.DatabaseName -in @("master", "tempdb", "model", "msdb")) { continue }

            try {
                $accessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

                $connection = New-Object System.Data.SqlClient.SqlConnection
                $connection.ConnectionString = "Server=$($server.ServerName).database.windows.net;Database=$($db.DatabaseName);Encrypt=True;TrustServerCertificate=False;"
                $connection.AccessToken = $accessToken
                $connection.Open()

                $command = $connection.CreateCommand()
                $command.CommandText = $sqlScript
                $reader = $command.ExecuteReader()

                while ($reader.Read()) {
                    $results += [PSCustomObject]@{
                        Subscription = $subscription.Name
                        Server       = $server.ServerName
                        Database     = $db.DatabaseName
                        Schema       = $reader["SchemaName"]
                        Table        = $reader["TableName"]
                        TotalRows    = $reader["TotalRows"]
                    }
                }

                $reader.Close()
                $connection.Close()
            }
            catch {
                Write-Warning "Failed to query $($db.DatabaseName) on $($server.ServerName): $_"
            }
        }
    }
}

# Output as table
$results | Sort-Object Subscription, Server, Database, TotalRows -Descending | Format-Table -AutoSize

The post Query multiple Azure SQL Databases appeared first on sqlkitty.