萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 表格自動建立索引的 Perl 腳本實作

針對大量資料表需依主鍵(Identity 欄位)自動建立唯一索引的需求,本文提供一套可直接部署的 Perl 腳本,透過 DBI 模組連線 SQL Server,自動掃描所有使用者資料表並補齊缺失索引。

核心邏輯流程

  1. 連線資料庫,查詢 sys.all_objects 取得所有使用者資料表(排除系統圖表 sysdiagrams)。
  2. 逐表檢查是否存在 Identity 欄位,若有則將該欄位作為索引鍵候選。
  3. 查詢 sysindexes 確認該欄位是否已有索引,若無則動態產生 CREATE UNIQUE INDEX 語句並執行。
  4. 將已處理的資料表名稱記錄至 createtableallindex.txt 供稽核追蹤。

關鍵程式碼片段

取得 Identity 欄位

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 模組達成同樣目標,維護性更佳。

copyright © 萬盛學電腦網 all rights reserved