修改使用者定義資料類型精度的完整解決方案

使用者定義資料類型 (UDT) 一經被資料表參考即無法直接修改或刪除。本文提供標準化的三步驟變更流程：重命名舊型別 → 建立新型別 → 批次 ALTER TABLE 切換欄位型別，並附完整動態 SQL 腳本。

核心限制

• sp_droptype 失敗：訊息 15170，型別已被參考。
• ALTER TYPE 不支援修改精度/長度 (SQL Server 至 2022 仍不支援)。

標準三步驟變更法

1. 重命名舊 UDT：EXEC sp_rename 'aa', 'aa_bak', 'USERDATATYPE';
2. 建立新 UDT：EXEC sp_addtype 'aa', 'numeric(20,2)', 'not null'; (新精度/長度/NULLability)
3. 批次切換資料表欄位：動態產生 ALTER TABLE ... ALTER COLUMN ... 陳述式逐表執行。

完整動態 SQL 腳本 (可直接執行)

-- 參數區：請修改以下三個變數
DECLARE @OldTypeName SYSNAME = 'aa';          -- 舊 UDT 名稱
DECLARE @NewTypeDef NVARCHAR(256) = 'numeric(20,2)'; -- 新型別定義
DECLARE @Nullability NVARCHAR(10) = 'NOT NULL';      -- NULL/NOT NULL

-- 1. 重命名舊型別
EXEC sp_rename @OldTypeName, @OldTypeName + '_bak', 'USERDATATYPE';

-- 2. 建立新型別
DECLARE @CreateTypeSQL NVARCHAR(MAX) = 
    N'EXEC sp_addtype @typename = ' + QUOTENAME(@OldTypeName, '''') + 
    N', @phystype = ' + QUOTENAME(@NewTypeDef, '''') + 
    N', @nulltype = ' + QUOTENAME(@Nullability, '''') + N';';
EXEC sp_executesql @CreateTypeSQL;

-- 3. 批次 ALTER TABLE 切換欄位型別
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += N'
ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + N'
ALTER COLUMN ' + QUOTENAME(c.name) + N' ' + @NewTypeDef + N' ' + @Nullability + N';'
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE t.name = @OldTypeName + '_bak'          -- 找出仍使用舊型別的欄位
  AND o.type = 'U';                           -- 僅使用者資料表

-- 列印並執行 (大量資料表建議分批執行並監控記錄檔)
PRINT @SQL;
-- EXEC sp_executesql @SQL;  -- 確認無誤後取消註解執行

進階考量與最佳實務

驗證腳本

-- 確認所有欄位已切換至新型別
SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    c.name AS ColumnName,
    t.name AS CurrentTypeName,
    c.max_length, c.precision, c.scale, c.is_nullable
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE t.name = @OldTypeName;  -- 應回傳 0 筆

-- 確認舊型別已無相依
SELECT * FROM sys.types WHERE name = @OldTypeName + '_bak'; -- 僅剩型別定義
-- 確認無物件參考舊型別
SELECT * FROM sys.sql_expression_dependencies WHERE referenced_id = TYPE_ID(@OldTypeName + '_bak');

替代方案：不使用 UDT

現代開發建議直接使用基礎型別 (DECIMAL(20,2), NVARCHAR(100)) 而非 UDT，優點：

• 避免型別變更鎖定資料表的運維痛點。
• 跨資料庫/伺服器部署無需同步 UDT 定義。
• ORM/程式碼產生工具相容性更好。

若業務強制要求集中管理型別定義，請建立型別變更標準作業程序 (SOP) 並納入變更管理流程。

1. 上一篇文章： SQL Server 安全性檢查清單：強化伺服器防護的 10 大關鍵項目
2. 下一篇文章： 使用 T-SQL 批次匯入目錄下所有檔案到 SQL Server (BULK INSERT / BCP)

關於SqlServer

SQL Server 2008 資料探勘完整流程六步驟：從定義問題到部署模型的實務指南

資料探勘是從大量資料中發現可行動模式的過程。SQL Serv

SQL Server 死鎖原理、排查與避免全攻略

SQL Server 死鎖原理、排查與避免全攻略 1. 死

SSIS 合併元件深度解析：Merge、Merge Join、Union All 差異與 Sort 同步/非同步特性

SSIS 資料流程提供三種合併資料的元件，功能相似但適用場景

SQL Server 2000 實戰：用 VBScript + DTS 自動化資料轉換與郵件報表推送

背景與目標 應用服務提供者常需「主動推送」資料給客戶，而非等