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(https://www.360docs.net/doc/9914081283.html,)
+N' ALTER COLUMN '+QUOTENAME(https://www.360docs.net/doc/9914081283.html,)
+N' test'
FROM sysobjects o,syscolumns c,systypes t
WHERE OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND https://www.360docs.net/doc/9914081283.html,='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'