針對大量資料表需依主鍵(Identity 欄位)自動建立唯一索引的需求,本文提供一套可直接部署的 Perl 腳本,透過 DBI 模組連線 SQL Server,自動掃描所有使用者資料表並補齊缺失索引。
sys.all_objects 取得所有使用者資料表(排除系統圖表 sysdiagrams)。sysindexes 確認該欄位是否已有索引,若無則動態產生 CREATE UNIQUE INDEX 語句並執行。createtableallindex.txt 供稽核追蹤。sub get_columns {
my ($table_name, $object_id) = @_;
my $sql = qq{
SELECT col.name, tp.name, col.max_length, col.precision, col.scale,
col.is_nullable, col.is_identity
FROM sys.all_columns col
INNER JOIN sys.types tp ON col.system_type_id = tp.system_type_id
AND col.user_type_id = tp.user_type_id
WHERE object_id = ?
};
my $sth = $dbh2->prepare($sql);
$sth->execute($object_id);
while (my @col = $sth->fetchrow_array()) {
my ($col_name, $type_name, $max_len, $prec, $scale, $is_null, $is_id) = @col;
if ($is_id == 1) {
$ok = 1;
$sort_column = $col_name; # 記錄 Identity 欄位名
}
}
}
sub do_sql {
my ($table, $index_col) = @_;
my $sql = "CREATE UNIQUE INDEX $index_col ON $table ($index_col)";
my $dbh = DBI->connect("dbi:ODBC:$source_name", $user, $pwd, {RaiseError=>1});
$dbh->do($sql);
print "已建立 $table.$index_col 索引\n";
}
DBI、DBD::ODBC 模組,並設定好系統 DSN(範例中名為 zoe)。ALTER 資料表權限。RaiseError => 1,遇錯誤即中止,避免產生不一致狀態。可依實際需求調整索引類型(叢集/非叢集)、加入填滿因子(FILLFACTOR)、或納入複合鍵邏輯。若環境為較新版 SQL Server,亦可改用 PowerShell + dbatools 模組達成同樣目標,維護性更佳。