204.修改已经被表引用的数据定义数据类型-案例

小鱼儿 2024-01-20 07:12 24阅读 0赞

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'