批次檢查資料庫中所有使用者資料表,若存在 IDENTITY 欄位 (自動遞增主鍵候選) 且尚未建立索引,則自動建立 UNIQUE INDEX。
sys.all_objects WHERE type='U' AND is_ms_shipped=0)is_identity = 1 的欄位sysindexes 是否已存在同名索引CREATE UNIQUE INDEX idx_col ON table(col)#!/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.indexesQUOTENAME / 方括號包裹物件名稱,防止保留字衝突BEGIN TRAN / COMMIT 批次提交sys.indexes 檢查索引存在性 (支援篩選索引、包含欄位等新特性)IX_Table_Column 而非直接用欄位名FILLFACTOR、DATA_COMPRESSION 等選項自動化索引建立腳本能快速補齊遺漏的主鍵索引,提升查詢效能。生產環境執行前務必備份、測試、安排維護窗口,並驗證索引使用情況 (sys.dm_db_index_usage_stats)。