sp_addconstraintudt(增加原有自定义数据类型用于主键的表的主键约束).sql

来自「SQL2005 不支持原有的SQL2000修改数据类型方式」· SQL 代码 · 共 61 行

if exists (select 1
          from sysobjects
          where name = 'sp_addconstraintUDT'
          and type = 'P')
   drop procedure sp_addconstraintUDT
go
/*******************************************************************************************
*	system:  管理系统
*	subsys:  数据中心
*	proc:    sp_addconstraintUDT
*	comm:	 sp_addconstraintUDT(增加原有自定义数据类型用于主键的表的主键约束).sql
*	created by yangsh in 2008-10-14
*	edit: 
*	edit remark: 
* debug:	 exec sp_addconstraintUDT 'u_brchno' 
*********************************************************************************************/
create procedure sp_addconstraintUDT 
(
	@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 pri_const
    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  pri_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='alter table ['+@tablna+'] add constraint  '+@pkname+' primary key (' +substring(@sql,2,len(@sql)) +' )'
     print @exsql
     exec (@exsql)
     fetch next from pknu into @tablna,@pkname
    end
  close pknu
  deallocate pknu
  print '成功增加原有主键约束！'

end