sp_dropconstraintudt(删除自定义数据类型用于主键的表的主键约束).sql

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

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


---有自定义类型做主键的表 ，主键约束名
    insert into pri_const (tablna,pkname,coluna,indest)
    select o.name as 表名,i.name as 主键约束名,c.name as 字段名,k.keyno as 索引顺序 
        from sysindexes i
        join sysindexkeys k on i.id = k.id and i.indid = k.indid
        join sysobjects o on i.id = o.id
        join syscolumns c on i.id=c.id and k.colid = c.colid
        join systypes d on (c.xusertype=d.xusertype and d.name=@usdftp)
    where o.xtype = 'U'
    and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)
    order by o.name,k.keyno
  
---有自定义类型做主键的表的主键字段
if not exists (select 1 from sysobjects where name ='pri_column' and type='U')
    create table pri_column
    ( 
      tablna varchar(30),--有自定义类型做主键的表
      pkname varchar(30),--主键约束名称
      coluna varchar(20),--主键的包含字段
      indest int       --索引顺序
     )
else 
   truncate table pri_column


insert into pri_column (tablna,pkname,coluna,indest)
select o.name as 表名,i.name as 主键约束名,c.name as 字段名,k.keyno as 索引顺序 
    from sysindexes i
    join sysindexkeys k on i.id = k.id and i.indid = k.indid
    join sysobjects o on i.id = o.id
    join syscolumns c on i.id=c.id and k.colid = c.colid
    join pri_const d on (o.name=d.tablna)
where o.xtype = 'U'
and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)
order by o.name,k.keyno


	declare @SQL varchar(2000)
	--定义游标，修改所有用到此自定义类型的字段
	declare tb cursor local	for 
	select 'alter table ['+a.tablna+'] drop constraint ['+a.pkname+'] '
		from pri_const 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