如何用PowerShell获取Azure订阅下所有SQL Server的全量表信息

前提准备 #

1. 安装必要的PowerShell模块：

   # 安装Az模块（用于管理Azure资源）
   Install-Module -Name Az -Force -AllowClobber
   # 安装SqlServer模块（用于执行SQL查询）
   Install-Module -Name SqlServer -Force -AllowClobber
   

2. 登录Azure并选择目标订阅：

   Connect-AzAccount
   # 替换为你的订阅ID
   Set-AzContext -SubscriptionId "你的订阅ID"
   

完整脚本实现 #

以下脚本会遍历指定订阅下的所有Azure SQL Server，跳过系统数据库，获取每个用户数据库中的所有用户表，并按要求格式输出：

# 获取订阅下所有Azure SQL Server实例
$sqlServers = Get-AzSqlServer

# 遍历每个SQL Server
foreach ($server in $sqlServers) {
    $serverName = $server.ServerName
    $serverFqdn = $server.FullyQualifiedDomainName
    $adminUser = $server.SqlAdministratorLogin

    # 获取服务器下的所有用户数据库（排除系统数据库）
    $userDatabases = Get-AzSqlDatabase -ServerName $serverName -ResourceGroupName $server.ResourceGroupName | 
                     Where-Object { $_.DatabaseName -notin @('master', 'model', 'msdb', 'tempdb') }

    # 遍历每个用户数据库
    foreach ($db in $userDatabases) {
        $dbName = $db.DatabaseName

        # 安全获取管理员密码（避免明文）
        $cred = Get-Credential -UserName $adminUser -Message "输入[$serverName]的SQL管理员密码"
        $password = $cred.GetNetworkCredential().Password

        # 构建SQL连接字符串
        $connString = "Server=tcp:$serverFqdn,1433;Database=$dbName;User ID=$adminUser;Password=$password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

        # 查询当前数据库的所有用户表
        $tables = Invoke-SqlCmd -ConnectionString $connString -Query @"
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
"@

        # 按要求格式输出结果
        foreach ($table in $tables) {
            [PSCustomObject]@{
                "表名" = $table.TABLE_NAME
                "数据库名" = $dbName
                "Azure SQL Server名称" = $serverName
                "Azure SQL Server域名" = $serverFqdn
            }
        }
    }
}

关键注意事项 #

• 权限要求：执行脚本的账号需要具备Azure资源的读取权限（如Reader角色），以及目标SQL Server的db_datareader权限（或更高权限）。
• 防火墙规则：确保执行PowerShell的客户端IP已添加到Azure SQL Server的防火墙允许列表中，否则会连接失败。
• 批量密码处理：如果服务器数量较多，每次输入密码效率低，可以考虑使用Azure Key Vault存储密码并自动读取，减少手动操作。

内容的提问来源于stack exchange，提问作者Oki Kuswanda