sp_dropindexudt(删除自定义数据类型用于索引约束).sql

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

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

---有自定义类型做约束的  表，索引名 
insert into index_name
select d.name as 表名,a.name as 索引名,c.name as 字段名,b.keyno as 索引顺序--,e.name as 自定义类型 
from sysindexes a
inner join sysindexkeys b on (a.id=b.id and a.indid=b.indid)
inner join syscolumns  c on (b.colid=c.colid and a.id=c.id)
inner join sysobjects d on (a.id=d.id)
inner join systypes e on (c.xusertype=e.xusertype and e.name=@usdftp)
where a.name like 'i%'---一时找不出自定义索引,偷懒用了 索引名 like 'i%'

  
---有自定义类型做主键的表的主键字段
if not exists (select 1 from sysobjects where name ='index_column' and type='U')
    create table index_column
    ( 
      tablna varchar(30),--有自定义类型做主键的表
      pkname varchar(30),--主键约束名称
      coluna varchar(20),--主键的包含字段
      indest int       --索引顺序
     )
else 
   truncate table index_column

insert into index_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 index_name d on (o.name=d.tablna and i.name=d.pkname)
order by o.name,k.keyno

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