您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

Perl 自動為 SQL Server 所有用戶表創建主鍵索引：系統表查詢與動態 DDL 實戰

編輯：關於SqlServer

自動化目標

遍歷數據庫所有用戶表（sys.all_objects type='U'），檢測是否存在標識列，若存在且無同名唯一索引，則自動創建 CREATE UNIQUE INDEX col_name ON table(col_name)。

核心邏輯流程

查詢用戶表：SELECT name, object_id FROM sys.all_objects WHERE type='U' AND is_ms_shipped=0 AND name<>'sysdiagrams'。
對每張表，查詢列屬性：
SELECT col.name, tp.name, col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity 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 object_id=?。
若發現 is_identity=1，記錄列名作為索引鍵 $sort_column。
檢查 sysindexes 是否已存在同名索引：SELECT * FROM sysindexes WHERE id=object_id('table') AND name='col_name'。
不存在則動態執行 CREATE UNIQUE INDEX col_name ON table(col_name)。
結果記錄至 createtableallindex.txt。

關鍵代碼片段

# 獲取標識列
sub get_columns {
    my ($table_name, $object_id) = @_;
    my $sql = "SELECT col.name, ..., col.is_identity FROM sys.all_columns col JOIN sys.types tp ... WHERE object_id=$object_id";
    my $sth = $dbh2->prepare($sql);
    $sth->execute();
    while (my @col = $sth->fetchrow_array()) {
        my ($col_name, ..., $is_identity) = @col;
        if ($is_identity == 1) {
            $ok = 1;
            $sort_column = $col_name;
        }
    }
}

# 創建索引
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);
}

⚠️ 風險與改進點

問題	影響	改進方案
假設標識列適合作唯一索引	標識列不一定是業務主鍵，可能重複（未聲明 PRIMARY KEY）	優先檢查 `sys.key_constraints` 找真正 PK，或要求人工確認
索引名直接用列名	多表同列名導致索引名衝突	命名規範：`UX_table_column`
無事務包裝	批量執行中途失敗難以回滾	單表單事務，記錄失敗列表供重跑
未考慮填充因子、壓縮、文件組	大表索引性能次優	參數化選項，或生成腳本供 DBA 審核後執行
Perl/ODBC 依賴環境脆弱	遷移/升級易失效	改用 PowerShell + SMO 或 T-SQL 動態腳本純數據庫端執行

純 T-SQL 替代方案（推薦）

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(''' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ''') AND name = ''UX_' + t.name + '_' + c.name + ''')
    CREATE UNIQUE INDEX UX_' + t.name + '_' + c.name + ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + '(' + QUOTENAME(c.name) + ');'
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.is_identity = 1
  AND NOT EXISTS (SELECT 1 FROM sys.key_constraints k WHERE k.parent_object_id = t.object_id AND k.type = 'PK'); -- 無顯式 PK
-- PRINT @sql; -- 審核後執行
-- EXEC sp_executesql @sql;

優勢：無外部依賴、集中審核、可並行、易納入發布流程。