修改已经被表引用的用户定义数据类型示例.sql

USE tempdb

--创建测试环境

--添加用户定义的数据类型
EXEC sp_addtype 'test','varchar(6)','NOT NULL'
GO

--创建引用用户定义类型的表
CREATE TABLE ta(col test)
CREATE TABLE tb(col test)
GO

--下面的示例演示把用户定义类型test修改为基类型为int,且允许NULL

--将用户定义类型test改名备份
EXEC sp_rename 'test','test_bak','USERDATATYPE'

--创建用户定义类型,基类型为nvarchar(6),允许NULL
EXEC sp_addtype 'test','int','NULL'

--通过游标,修改所有引用了数据类型test的列为新定义的数据类型
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

--下面在测试表中插入数据,验证修改后的效果（由于修改后的数据类型为int，不能接受char值，所以插入会失败）
INSERT ta VALUES('a')
/*--结果
服务器: 消息 245，级别 16，状态 1，行 1
将 varchar 值 'a' 转换为数据类型为 int 的列时发生语法错误。
--*/
GO

INSERT tb VALUES('b')
/*--结果
服务器: 消息 245，级别 16，状态 1，行 1
将 varchar 值 'b' 转换为数据类型为 int 的列时发生语法错误。
--*/
GO

--删除测试
DROP TABLE ta,tb
EXEC sp_droptype 'test'