USE tempdb

--創(chuàng)建測試環(huán)境

--添加用戶定義的數(shù)據(jù)類型
EXEC sp_addtype 'test','varchar(6)','NOT NULL'
GO

--創(chuàng)建引用用戶定義類型的表
CREATE TABLE ta(col test)
CREATE TABLE tb(col test)
GO

--下面的示例演示把用戶定義類型test修改為基類型為int,且允許NULL

--將用戶定義類型test改名備份
EXEC sp_rename 'test','test_bak','USERDATATYPE'

--創(chuàng)建用戶定義類型,基類型為nvarchar(6),允許NULL
EXEC sp_addtype 'test','int','NULL'

--通過游標,修改所有引用了數(shù)據(jù)類型test的列為新定義的數(shù)據(jù)類型
DECLARE tb CURSOR LOCAL
FOR
SELECT N'ALTER TABLE '+QUOTENAME(o.name)
	+N' ALTER COLUMN '+QUOTENAME(c.name)
	+N' test'
FROM sysobjects o,syscolumns c,systypes t
WHERE OBJECTPROPERTY(o.id,N'IsUserTable')=1
	AND t.Name='test_bak'
	AND o.id=c.id
	AND c.xusertype=t.xusertype
DECLARE @sql nvarchar(1000)
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC(@sql)
	FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb

--刪除舊的用戶定義類型test_bak
EXEC sp_droptype 'test_bak'
GO

--下面在測試表中插入數(shù)據(jù),驗證修改后的效果（由于修改后的數(shù)據(jù)類型為int，不能接受char值，所以插入會失敗）
INSERT ta VALUES('a')
/*--結(jié)果
服務器: 消息 245，級別 16，狀態(tài) 1，行 1
將 varchar 值 'a' 轉(zhuǎn)換為數(shù)據(jù)類型為 int 的列時發(fā)生語法錯誤。
--*/
GO

INSERT tb VALUES('b')
/*--結(jié)果
服務器: 消息 245，級別 16，狀態(tài) 1，行 1
將 varchar 值 'b' 轉(zhuǎn)換為數(shù)據(jù)類型為 int 的列時發(fā)生語法錯誤。
--*/
GO

--刪除測試
DROP TABLE ta,tb
EXEC sp_droptype 'test'