萬盛學電腦網

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

Perl 自動為 SQL Server 所有使用者表建立主鍵欄位唯一索引

專案需求

批次檢查資料庫中所有使用者資料表,若存在 IDENTITY 欄位 (自動遞增主鍵候選) 且尚未建立索引,則自動建立 UNIQUE INDEX。

核心流程

  1. 查詢所有使用者表 (sys.all_objects WHERE type='U' AND is_ms_shipped=0)
  2. 查詢各表欄位,找出 is_identity = 1 的欄位
  3. 檢查 sysindexes 是否已存在同名索引
  4. 若不存在,執行 CREATE UNIQUE INDEX idx_col ON table(col)
  5. 記錄日誌

完整 Perl 腳本

#!/usr/bin/perl
use DBI; use strict; use warnings;

my $dsn = "dbi:ODBC:zoe";
my $user = "sa"; my $pass = "123";

my $dbh = DBI->connect($dsn, $user, $pass) or die $DBI::errstr;

# 取得所有使用者表
my $sth = $dbh->prepare(q{
    SELECT name, object_id 
    FROM sys.all_objects 
    WHERE type='U' AND is_ms_shipped=0 AND name <> 'sysdiagrams'
});
$sth->execute();

while (my ($tname, $tid) = $sth->fetchrow_array) {
    # 找 IDENTITY 欄位
    my $col_sql = q{
        SELECT col.name 
        FROM sys.all_columns col
        JOIN sys.types tp ON col.system_type_id=tp.system_type_id AND col.user_type_id=tp.user_type_id
        WHERE col.object_id = ? AND col.is_identity = 1
    };
    my $csth = $dbh->prepare($col_sql);
    $csth->execute($tid);
    my $col_name = $csth->fetchrow_array;
    $csth->finish;
    next unless $col_name;

    # 檢查索引是否存在
    my $idx_name = $col_name;  -- 索引名同欄位名
    my $chk_sql = "SELECT 1 FROM sysindexes WHERE id = OBJECT_ID(?) AND name = ?";
    my $chk_sth = $dbh->prepare($chk_sql);
    $chk_sth->execute($tname, $idx_name);
    next if $chk_sth->fetchrow_array;  -- 已存在
    $chk_sth->finish;

    # 建立唯一索引
    my $create_sql = "CREATE UNIQUE INDEX [$idx_name] ON [$tname] ([$idx_name])";
    print "建立索引: $create_sql\n";
    $dbh->do($create_sql) or warn "建立失敗: $DBI::errstr";
    
    # 記錄
    open my $fh, '>>', 'createtableallindex.txt' or die $!;
    print $fh "$tname\n";
    close $fh;
}

$sth->finish;
$dbh->disconnect;
print "所有表索引檢查建立完成\n";

關鍵技術點

  • sys.all_columns + sys.types 取得完整欄位中繼資料
  • is_identity 識別自動遞增欄位
  • sysindexes 相容性檢查 (舊版) / 建議改用 sys.indexes
  • QUOTENAME / 方括號包裹物件名稱,防止保留字衝突
  • 交易控制:腳本未顯式使用交易,建議加入 BEGIN TRAN / COMMIT 批次提交

改進建議

  1. 改用 sys.indexes 檢查索引存在性 (支援篩選索引、包含欄位等新特性)
  2. 索引命名規範:IX_Table_Column 而非直接用欄位名
  3. 加入 FILLFACTOR、DATA_COMPRESSION 等選項
  4. 支援複合主鍵 (多欄位 IDENTITY 極少見,但邏輯上可擴展)
  5. 輸出詳細日誌:時間、表名、欄位、索引名、耗時、成功/失敗

總結

自動化索引建立腳本能快速補齊遺漏的主鍵索引,提升查詢效能。生產環境執行前務必備份、測試、安排維護窗口,並驗證索引使用情況 (sys.dm_db_index_usage_stats)。

copyright © 萬盛學電腦網 all rights reserved