修改使用者定義資料類型的通用預存程序

痛點

使用者定義型別 (UDT) 一經被資料表參考，即無法直接 ALTER 或 DROP，修改精度極為麻煩。

解決方案：兩階段預存程序

核心程序：sp_rechangefieldtype

遍歷所有參考該 UDT 的資料行，動態執行 ALTER TABLE ... ALTER COLUMN 變更型別，並自動處理預設值約束的移除/重建。

包裝程序：SP_CHANGEFIELD

CREATE PROCEDURE SP_CHANGEFIELD 
  @OLDTYPENAME VARCHAR(50), 
  @NEWDTYPE VARCHAR(50)
AS
BEGIN
  -- 1. 建立暫時型別
  EXEC('sp_addtype U_LOCALTYPE, ''' + @NEWDTYPE + '''')
  
  -- 2. 將所有參考舊型別的資料行改為暫時型別
  EXEC sp_rechangefieldtype @OLDTYPENAME, 'U_LOCALTYPE'
  
  -- 3. 重建所有檢視表
  EXEC sp_rebuildallview
  
  -- 4. 刪除舊型別
  EXEC('sp_droptype ' + @OLDTYPENAME)
  
  -- 5. 以原名建立新型別
  EXEC('sp_addtype ' + @OLDTYPENAME + ', ''' + @NEWDTYPE + '''')
END

使用範例

-- 將 UDT 'MyInt' 從 INT 改為 BIGINT
EXEC SP_CHANGEFIELD 'MyInt', 'BIGINT'

關鍵技術點

• 游標遍歷 syscolumns、systypes、sysobjects 系統表
• 動態 SQL 處理預設值約束 (cdefault)
• 暫時型別作為中介，繞過「無法刪除被參考型別」限制
• 最後 sp_rebuildallview 確保檢視表元資料同步

權限需求

執行者需具備 db_owner、db_ddladmin 或物件擁有者權限。