sp_updateudt(自定义数据类型修改).sql

SQL2005 不支持原有的SQL2000修改数据类型方式

if exists (select 1
          from sysobjects
          where name = 'sp_updateUDT'
          and type = 'P')
   drop procedure sp_updateUDT
go
/*******************************************************************************************
*	system:  管理系统
*	subsys:  数据中心
*	proc:    sp_updateUDT
*	comm:	   sp_updateUDT(自定义数据类型修改).sql 适用与自定义数据类型没有用到主键的表 的修改
*	created by wyb in 2008-10-12
*	edit: 
*	edit remark: 
* debug:	 exec sp_updateUDT 'u_cityno' ,'varchar(4)','0'
*********************************************************************************************/
create procedure sp_updateUDT 
(
	@usdftp varchar(20),		--自定义数据类型
	@newdtp varchar(100),		--自定义数据类型的新数据类型
	@nullfg char(1)					--是否为null 1 null ,0 not null
)
as
begin
	print  '开始修改自定义类型！'
	declare @SQL varchar(2000)
	--将原有的自定义数据更名
	select @SQL='	exec sp_rename '''+@usdftp+''','''+@usdftp+'_bak'',''USERDATATYPE'''
    print @SQL	
	exec(@SQL)
	--修改原有的自定义数据类型
	if @nullfg='0'
		select @SQL='EXEC sp_addtype '''+@usdftp+''', '''+@newdtp+''', ''null'''
	else
		select @SQL='EXEC sp_addtype '''+@usdftp+''', '''+@newdtp+''', ''not null'''
	exec(@SQL)
	--定义游标，修改所有用到此自定义类型的字段,--edity by yangsh 原有字段 是否为空也要加判断 isnullable
	declare tb cursor local	for 
	select 'alter table ['+object_name(a.id)+'] alter column ['+a.name+'] '+@usdftp + case when a.isnullable=0 then '  not null' else '  null' end
		from syscolumns a 
		join systypes b on a.xusertype=b.xusertype
	where b.name=@usdftp +'_bak'
	open tb
	fetch next from tb into @SQL
	while @@fetch_status=0
	begin
	 exec(@SQL)
     print @SQL
	 fetch next from tb into @SQL
	end
	close tb
	deallocate tb
	--删除更名后的自定义数据类型
	select @SQL='drop type ' + @usdftp +'_bak'
	exec(@SQL)
    print @SQL
    print '成功修改自定义数据类型'
end