修改已经被表引用的用户定义数据类型示例.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'