如何批量导出多Azure SQL数据库中自动调优创建的索引脚本？

开发者阿泽

2026-5-12

没问题！针对多Azure SQL数据库批量导出自动调优创建的索引脚本，确实有两种高效的方案，完全不用手动挨个点建议窗口。下面给你详细拆解：

方法一：用PowerShell批量处理所有数据库

这种方式适合一次性搞定多个数据库，省得重复操作。步骤如下：

先确保你本地安装了Az.Sql模块，如果没装的话，先跑这个命令：
```
Install-Module -Name Az.Sql -Force -AllowClobber
```
登录你的Azure账号：
```
Connect-AzAccount
```
切换到目标订阅（如果有多个订阅的话）：
```
Set-AzContext -Subscription "你的订阅ID或名称"
```

定义要处理的服务器和资源组，然后遍历所有用户数据库：

# 替换成你的服务器和资源组名称
$serverName = "your-sql-server-name"
$resourceGroupName = "your-resource-group-name"
# 获取所有非master的用户数据库
$databases = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName | Where-Object { $_.DatabaseName -ne "master" }

遍历每个数据库，查询自动调优创建的索引并导出脚本：

foreach ($db in $databases) {
    $dbName = $db.DatabaseName
    Write-Host "正在处理数据库: $dbName"
    
    # 执行SQL查询获取索引创建脚本
    $indexScripts = Invoke-AzSqlDatabaseQuery -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $dbName -Query @"
        SELECT 
            SCHEMA_NAME(t.schema_id) AS SchemaName,
            t.name AS TableName,
            i.name AS IndexName,
            CREATE_INDEX_STATEMENT = 'CREATE NONCLUSTERED INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] (' + 
                STUFF((SELECT ', [' + c.name + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
                        FROM sys.index_columns ic
                        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
                        ORDER BY ic.key_ordinal
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
            ') ' + 
            CASE WHEN EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) THEN
                'INCLUDE (' + STUFF((SELECT ', [' + c.name + ']'
                                    FROM sys.index_columns ic
                                    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                                    WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
                                    ORDER BY ic.index_column_id
                                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
            ELSE '' END
        FROM sys.indexes i
        JOIN sys.tables t ON i.object_id = t.object_id
        WHERE i.is_auto_created = 1 AND i.type_desc = 'NONCLUSTERED'
"@
    
    # 导出到CSV文件（方便查看）
    $indexScripts.Rows | Select-Object SchemaName, TableName, IndexName, CREATE_INDEX_STATEMENT | Export-Csv -Path ".\$dbName-auto-tune-indexes.csv" -NoTypeInformation
    
    # 同时导出到SQL文件（直接可执行）
    $indexScripts.Rows.CREATE_INDEX_STATEMENT | Out-File -Path ".\$dbName-auto-tune-indexes.sql" -Encoding utf8
}

这里核心是用i.is_auto_created = 1筛选出自动调优创建的索引，这个字段是SQL Server专门标记自动创建索引的标识。

方法二：用SQL脚本单库查询（可结合工具批量执行）

如果你习惯用SSMS或者Azure Data Studio，也可以用这个SQL语句直接查询单个数据库的自动调优索引脚本，然后可以用工具的批量执行功能跑多个数据库：

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    CREATE_INDEX_STATEMENT = 'CREATE NONCLUSTERED INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] (' + 
        STUFF((SELECT ', [' + c.name + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
                FROM sys.index_columns ic
                JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
                ORDER BY ic.key_ordinal
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
    ') ' + 
    CASE WHEN EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) THEN
        'INCLUDE (' + STUFF((SELECT ', [' + c.name + ']'
                            FROM sys.index_columns ic
                            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                            WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
                            ORDER BY ic.index_column_id
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
    ELSE '' END
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_auto_created = 1 AND i.type_desc = 'NONCLUSTERED'

这个查询会生成完整的索引创建语句，包含键列的排序方向、包含列，和自动调优创建的索引完全一致。

注意事项

权限要求：你的账号需要Azure SQL数据库的VIEW DEFINITION和SELECT权限，用PowerShell的话还需要Azure资源的读取权限。
自动调优创建的都是非聚集索引，所以我们过滤了type_desc = 'NONCLUSTERED'，如果有特殊情况可以去掉这个条件，但一般不需要。
导出的脚本可以直接在其他环境执行，或者作为备份留存。

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

如何批量导出多Azure SQL数据库中自动调优创建的索引脚本？

注意事项 #

注意事项