sp_addindexudt(增加原有自定义数据类型用于索引约束).sql

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

if exists (select 1
          from sysobjects
          where name = 'sp_addindexUDT'
          and type = 'P')
   drop procedure sp_addindexUDT
go
/*******************************************************************************************
*	system:  管理系统
*	subsys:  数据中心
*	proc:    sp_addindexUDT
*	comm:	 sp_addindexUDT(增加原有自定义数据类型用于索引约束).sql
*	created by yangsh in 2008-10-14
*	edit: 
*	edit remark: 
* debug:	 exec sp_addindexUDT 'u_brchno' 
*********************************************************************************************/
create procedure sp_addindexUDT 
(
	@usdftp varchar(20)		--自定义数据类型
)
as
begin
    print '开始增加原来删除的主键约束'
    declare @exsql varchar(2000)
        set @exsql=''
    declare @tablna varchar(30)
    declare @pkname varchar(100)

    declare pknu cursor local for
    select tablna,pkname
    from index_name
    open pknu
    fetch next from pknu into @tablna,@pkname
    while @@fetch_status=0
        begin
            declare @column varchar(20)
            declare @sql  varchar(2000)
                set @sql=''
            declare colu  cursor local for
            select coluna from  index_column
                where tablna=@tablna and pkname=@pkname
            order by indest asc
            open colu 
            fetch next from colu into @column
            while @@fetch_status=0
	              begin
	                  set @sql=@sql+','+@column
	          fetch next from colu into @column
            end
	          close colu
            deallocate colu
     select @exsql='create index ['+@pkname+'] on ['+@tablna+'](' +substring(@sql,2,len(@sql)) +' )'
     print @exsql
     exec (@exsql)
     fetch next from pknu into @tablna,@pkname
    end
  close pknu
  deallocate pknu
  print '成功增加原有索引约束！'

end