sp_dropdconstraintudt(删除默认值约束).sql

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

if exists (select 1
          from sysobjects
          where name = 'sp_dropDconstraintUDT'
          and type = 'P')
   drop procedure sp_dropDconstraintUDT
go
/*******************************************************************************************
*	system:  管理系统
*	subsys:  数据中心
*	proc:    sp_dropDconstraintUDT
*	comm:	 sp_dropDconstraintUDT(删除默认值约束).sql
*	created by yangsh in 2008-10-14
*	edit: 删除之前先要把原先的默认值的字段 插入到表中存储，以便之后增加默认值约束的时候还原有的约束
*	edit remark: 
* debug:	 exec sp_dropDconstraintUDT 'u_brchno'
*********************************************************************************************/
create procedure sp_dropDconstraintUDT 
(
	@usdftp varchar(20)		--自定义数据类型
)
as
begin
	print '开始删除自定义类型做默认值的约束！'
----自定义类型作为主键的表 保存到 pri_const
if not exists (select 1 from sysobjects where name ='df_name' and type='U')
   create table df_name
      (
        tablna varchar(30),--有自定义类型做主键的表
        pkname varchar(30),--约束名称
        coluna varchar(20),--
        indest varchar(30)       --默认值
      )
else 
  truncate table df_name

---有自定义类型做约束的  表，索引名 
insert into df_name

select c.name as 表名,d.name as 约束,a.name as 列名 ,substring(b.text,3,len(b.text)-4) as 默认值
from syscolumns a
inner join syscomments b on (a.cdefault=b.id)
inner join sysobjects c on (a.id=c.id)
inner join sysobjects d on (a.cdefault=d.id)
inner join systypes e on (a.xusertype=e.xusertype and e.name=@usdftp)
order by c.name,a.name


	declare @SQL varchar(2000)
	--定义游标，修改所有用到此自定义类型的字段
	declare tb cursor local	for 
	select 'alter table ['+a.tablna+'] drop constraint ['+a.pkname+']'
		from df_name a 
	open tb
	fetch next from tb into @SQL
	while @@fetch_status=0
	begin
	 exec(@SQL)
	 --select  @SQL
     print @SQL
    fetch next from tb into @SQL
	end
	close tb
	deallocate tb
   print '成功删除自定义类型做默认值的表的字段！'
	
end