Get All Table from All database with All Azure SQL Server in one Subscription

Question

I have subscription in Azure and i have lot Azure SQL Server. we need to list as below:

get all table | database name | name of Azure SQL Server | domain Azure SQL Server . maybe we can try with Powershell. but we just get list all Azure SQL Server.

Thank You

Answer 1

Yes, you can do that:

# Authenticate to Azure using an account with sufficient permissions
Connect-AzAccount

# Get all Azure SQL servers in the subscription
$azureSqlServers = Get-AzSqlServer

# Loop through each server and get the list of databases and tables
foreach ($server in $azureSqlServers) {
    Write-Output "Server Name: $($server.ServerName), Domain: $($server.ResourceGroupName)"

    # Get the list of databases in the current server
    $databases = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName

    Write-Output "Number of Databases: $($databases.Count)"
    foreach ($database in $databases) {
        Write-Output "Database Name: $($database.DatabaseName)"

        # Get the list of tables in the current database
        $tables = Invoke-Sqlcmd -ServerInstance "$($server.ServerName).database.windows.net" -Database $database.DatabaseName -Username $server.AdministratorLogin -Password $server.AdministratorLoginPassword -Query "SELECT * FROM information_schema.tables"

        # Output the list of tables
        Write-Output "Number of Tables: $($tables.Count)"
        foreach ($table in $tables) {
            Write-Output "Table Name: $($table.TABLE_NAME)"
        }
    }
}