表修改字段存储过程

转载 2007年09月28日 15:09:00

--alter table a  add  default '44' for right2

--exec usp_Table_ChangeField  'a', 'right2',  'varchar(20) default ''55'''
--select substring('char(20) default ''44''',1, charindex('default','char(20) default ''44''',1)-1)
/*
Author: LB, Date : 2005-04-12
Desc  : 用于修改表字段结构,包括已复制的表和未复制的表 
修改字段前,需删除该字段上的默认值, 约束, 唯一性, 索引, 再删除该字段.
*/

CREATE proc dbo.usp_Table_ChangeField
@TableName varchar(50),
@FieldName varchar(50),
@FieldAttribute varchar(200)

as
set nocount on
--set @TableName = 'ITM_SALES_MSTR'
--set @TempFieldName = 'TMP_UNITSQFT'
--SET @FieldName = 'UNITSQFT'
--set @FieldAttribute = 'Numeric(18,4) Null'
--EXEC SP_REPLDROPCOLUMN  @TableName, @TempFieldName
declare @errMsg nvarchar(400), @TempFieldName varchar(50)
set @errMsg = N'表
'+@TableName+N'中字段'+@FieldName+N'结构修改成功!'

if not exists(select name from sysobjects where xtype = 'U' and name = @TableName)
begin
    set @errMsg = N'表
'+@TableName+ N'不存在, 请检查!'
    print @errMsg
    return
end


if not exists(select l.name,r.name from sysobjects l inner join syscolumns r on l.id=r.id and r.name=@FieldName and l.name=@TableName  and l.xtype='U')
begin
       set @errMsg = N'表
'+@TableName+N'中字段'+@FieldName+N'不存在, 无法修改,请检查!'
    print @errMsg
    return
end

set @TempFieldName = @TableName + '_TempField'

Begin tran

if exists(select * from sysobjects where xtype='U' and replinfo != 0 and name = @TableName)
begin
--Drop Default
 if exists(select TbName=so2.Name,ColName=sc.Name,DfName=so.Name from SysObjects so inner join SysComments scm on so.Id=scm.id inner join SysColumns sc on sc.id=so.Parent_Obj and sc.cdefault=scm.id inner join SysObjects so2 on so2.id=so.Parent_Obj where
so2.name=@TableName and sc.Name=@FieldName and so.xtype in('D'))
 begin   --Delete default with the column
    declare @DfName0 varchar(100)
     select @DfName0=so.Name from SysObjects so inner join SysComments scm on so.Id=scm.id inner join SysColumns sc on sc.id=so.Parent_Obj and sc.cdefault=scm.id inner join SysObjects so2 on so2.id=so.Parent_Obj where
so2.name=@TableName and sc.Name=@FieldName and so.xtype in('D')
     set @DfName0='alter table '+ @TableName +' drop constraint '+ @DfName0
     exec(@DfName0)
     if @@error != 0
     begin
       set @errMsg = N'删除表
'+@TableName+N'默认值'+@DfName0+N'失败,请检查!'
         goto errHandle
     end
 end

 --Drop Check
 declare @CkName0 nvarchar(50),@CkSql0 nvarchar(100)
 if exists(select so.Name,sc.Text from SysComments sc inner join SysObjects so on so.ID=sc.ID inner join SysObjects so2 on so2.ID=so.Parent_Obj where so.xType='C' and so2.Name
=@TableName and sc.text like '%[['+@FieldName+']]%')
 begin
  if exists(select * from tempdb.dbo.SysObjects where name like '%#CkTmpTest%')   --Save Check to tmp table
   delete from #CkTmpTest
  else
   create table #CkTmpTest([Name] nvarchar(50),[Text] nvarchar(100))
    while exists(select so.Name,sc.Text from SysComments sc inner join SysObjects so on so.ID=sc.ID inner join SysObjects so2 on so2.ID=so.Parent_Obj where so.xType='C' and so2.Name
=@TableName and sc.text like '%[['+@FieldName+']]%')
    begin
   select @CkName0=so.Name, @CkSql0 = sc.Text from SysComments sc inner join SysObjects so on so.ID=sc.ID inner join SysObjects so2 on so2.ID=so.Parent_Obj where so.xType='C' and so2.Name
=@TableName and sc.text like '%[['+@FieldName+']]%'
   --删除字段前, 保留Check约束
   insert into #CkTmpTest ([Name],[Text]) Values(@CkName0,@CkSql0)
       set @CkSql0='alter table '+ @TableName +' drop constraint
'+@CkName0
       exec(@CkSql0)
       if @@error != 0
       begin
          set @errMsg = N'删除表
'+@TableName+N'Check约束'+@CkName0+N'失败,请检查!'
          goto errHandle
       end
    end
 end

 --Drop Uniqe
 if exists(select so.Name from SysObjects so inner join SysObjects so2 on so2.ID=so.Parent_Obj inner join SysIndexes si on si.ID=so.Parent_Obj and so.Name=si.Name inner join SysIndexKeys sk on sk.ID=si.ID and si.indid=sk.indid inner join SysColumns sc on sc.ID=sk.ID and sk.ColID=sc.ColID where so.xType='UQ' and so.Parent_Obj in (select ID from SysObjects where xType='U' and Name = @TableName) and sc.Name = @FieldName)
 begin
  if exists(select * from tempdb.dbo.SysObjects where name like '%#UQTmpTest%')
     delete from #UQTmpTest
  else
       create table #UQTmpTest([Name] nvarchar(50))
  declare @UqeName0 nvarchar(50),@UqeSql0 nvarchar(200)
    while exists(select so.Name from SysObjects so inner join SysObjects so2 on so2.ID=so.Parent_Obj inner join SysIndexes si on si.ID=so.Parent_Obj and so.Name=si.Name inner join SysIndexKeys sk on sk.ID=si.ID and si.indid=sk.indid inner join SysColumns sc on sc.ID=sk.ID and sk.ColID=sc.ColID where so.xType='UQ' and so.Parent_Obj in (select ID from SysObjects where xType='U' and Name = @TableName) and sc.Name = @FieldName)
    begin
       select @UqeName0=so.Name from SysObjects so inner join SysObjects so2 on so2.ID=so.Parent_Obj inner join SysIndexes si on si.ID=so.Parent_Obj and so.Name=si.Name inner join SysIndexKeys sk on sk.ID=si.ID and si.indid=sk.indid inner join SysColumns sc on sc.ID=sk.ID and sk.ColID=sc.ColID where so.xType='UQ' and so.Parent_Obj in (select ID from SysObjects where xType='U' and Name = @TableName) and sc.Name = @FieldName
   --删除字段前, 保留唯一性约束
   insert into #UQTmpTest Values(@UqeName0)
       set @UqeSql0='ALTER TABLE '+ @TableName +' Drop CONSTRAINT ['+ @UqeName0 +']'
       exec(@UqeSql0)
       if @@error != 0
       begin
          set @errMsg = N'删除表
'+@TableName+N'唯一性约束'+@UqeName0+N'失败,请检查!'
          goto errHandle
       end
    end
 end

 --Drop Primary Key
 declare @IdxName0 nvarchar(50),@IdxSql0 nvarchar(200)
 if exists(select B.NAME from (select * from sysobjects where xtype='U') A INNER JOIN SYSINDEXES B ON A.ID=B.ID  INNER JOIN SYSINDEXKEYS C ON A.ID=C.ID AND B.INDID=C.INDID INNER JOIN SYSCOLUMNS D ON A.ID=D.ID AND C.COLID=D.COLORDER WHERE A.ID=OBJECT_ID(@TableName) AND D.NAME
=@FieldName and b.dpages != 0 and b.name in ( select name from sysobjects where xtype = 'PK' ))
 begin
    SELECT @IdxName0= B.NAME from (select * from sysobjects where xtype='U') A INNER JOIN SYSINDEXES B ON A.ID=B.ID  INNER JOIN SYSINDEXKEYS C ON A.ID=C.ID AND B.INDID=C.INDID INNER JOIN SYSCOLUMNS D ON A.ID=D.ID AND C.COLID=D.COLORDER WHERE A.ID=OBJECT_ID(@TableName) AND D.NAME
=@FieldName and b.dpages != 0 and b.name in ( select name from sysobjects where xtype = 'PK' )
    set @IdxSql0 = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @IdxName0  
     exec(@IdxSql0)
     if @@error != 0
    begin
   set @errMsg = N'删除表
'+@TableName+N'主键'+@IdxName0+N'失败,请检查!'
   goto errHandle
     end
 end

 if exists(select B.NAME from (select * from sysobjects where xtype='U') A INNER JOIN SYSINDEXES B ON A.ID=B.ID  INNER JOIN SYSINDEXKEYS C ON A.ID=C.ID AND B.INDID=C.INDID INNER JOIN SYSCOLUMNS D ON A.ID=D.ID AND C.COLID=D.COLORDER WHERE A.ID=OBJECT_ID(@TableName) AND D.NAME =@FieldName and B.NAME NOT LIKE '_WA_Sys_'+@FieldName+'%')
 begin
  if exists(select * from tempdb.dbo.SysObjects where name like '%#IdxTmpTest%')
     delete from #IdxTmpTest
  else
       create table #IdxTmpTest([IdxName] nvarchar(100),KeyNo int,[ColName] nvarchar(100) )

  while exists(select distinct B.NAME from (select * from sysobjects where xtype='U') A INNER JOIN SYSINDEXES B ON A.ID=B.ID  INNER JOIN SYSINDEXKEYS C ON A.ID=C.ID AND B.INDID=C.INDID INNER JOIN SYSCOLUMNS D ON A.ID=D.ID AND C.COLID=D.COLORDER WHERE A.ID=OBJECT_ID(@TableName) AND D.NAME =@FieldName and B.NAME NOT LIKE '_WA_Sys_'+@FieldName+'%')
     begin
   --1. 查找Index Name
       SELECT distinct top 1 @IdxName0 = B.NAME from (select * from sysobjects where xtype='U') A INNER JOIN SYSINDEXES B ON A.ID=B.ID  INNER JOIN SYSINDEXKEYS C ON A.ID=C.ID AND B.INDID=C.INDID INNER JOIN SYSCOLUMNS D ON A.ID=D.ID AND C.COLID=D.COLORDER WHERE A.ID=OBJECT_ID(@TableName) AND D.NAME
=@FieldName and B.NAME NOT LIKE '_WA_Sys_'+@FieldName+'%'

   --2. 根据Index Name查找该索引所对应的所有字段
   insert #IdxTmpTest(IdxName, KeyNo, ColName) 
   SELECT  b.name, c.keyno, d.name from (select * from sysobjects where xtype = 'U') A INNER JOIN SYSINDEXES B ON A.ID=B.ID INNER JOIN SYSINDEXKEYS C ON A.ID=C.ID AND B.INDID=C.INDID INNER JOIN SYSCOLUMNS D ON A.ID=D.ID AND C.COLID=D.COLORDER
   WHERE A.ID=OBJECT_ID(@TableName) AND b.NAME = @IdxName0

        set @IdxSql0='drop index '+ @TableName +'.'+@IdxName0
       exec(@IdxSql0)
         if @@error != 0
         begin
          set @errMsg = N'删除表
'+@TableName+N'索引'+@IdxName0+N'失败,请检查!'
            goto errHandle
         end
     end
 end
end

declare @Def varchar(200)
set @def = ''
if CHARINDEX('CHAR', @FieldAttribute, 1) > 0
   set @FieldAttribute = substring(@FieldAttribute,1,  CHARINDEX(')',@FieldAttribute, 1)) +'  COLLATE SQL_Latin1_General_CP1_CI_AS ' +
     substring(@FieldAttribute, CHARINDEX(')',@FieldAttribute,1)+1, len(@FieldAttribute)-CHARINDEX(')',@FieldAttribute,1))

if CHARINDEX('default', @FieldAttribute ,1) > 0
begin
   set @def = rtrim(substring(@FieldAttribute, CHARINDEX('default',@FieldAttribute,1), 200-CHARINDEX('default',@FieldAttribute,1)-1))
   set @FieldAttribute = rtrim(substring(@FieldAttribute,1,  CHARINDEX('default',@FieldAttribute, 1)-1))
end
if exists(select * from sysobjects where xtype='U' and replinfo != 0 and name = @TableName)
begin
    --表已建立复制,使用复制相关存储过程修改字段
    exec SP_REPLADDCOLUMN @TableName,@TempFieldName,@FieldAttribute
    if @@error != 0
    begin
        set @errMsg = N'表
'+@TableName+N'增加临时字段'+@TempFieldName+N'失败,请检查!'
        goto errHandle
     end
    exec('UPDATE '+  @TableName +' SET '+ @TempFieldName +'='+ @FieldName)
    if @@error != 0
    begin
        set @errMsg = N'表
'+@TableName+N'临时字段'+@TempFieldName+N'赋值失败,请检查!'
        goto errHandle
    end

    EXEC SP_REPLDROPCOLUMN  @TableName,@FieldName
    if @@error != 0
    begin
        set @errMsg = N'表
'+@TableName+N'删除字段'+@FieldName+N'失败,请检查!'
        goto errHandle
    end
    EXEC SP_REPLADDCOLUMN @TableName,@FieldName,@FieldAttribute
    if @@error != 0
    begin

        set @errMsg = N'表'+@TableName+N'增加字段'+@FieldName+N'失败,请检查!'
        goto errHandle
    end
       exec('UPDATE '+ @TableName +' SET '+ @FieldName +'= '+ @TempFieldName)
    if @@error != 0
    begin
        set @errMsg = N'表
'+@TableName+N'字段'+@FieldName+N'赋值失败,请检查!'
        goto errHandle
    end
    EXEC SP_REPLDROPCOLUMN  @TableName, @TempFieldName
    if @@error != 0
    begin
        set @errMsg = N'表
'+@TableName+N'删除临时字段'+@TempFieldName+N'失败,请检查!'
        goto errHandle
    end

 --Add check
   if exists(select * from tempdb.dbo.SysObjects where name like '%#CkTmpTest%')   --add the check with the column
 begin
  declare @CkName1 nvarchar(50),@CkContent1 nvarchar(50),@CkSql1 nvarchar(100)
      while exists(select * from #CkTmpTest)
      begin
       select top 1 @CkName1=[Name],@CkContent1=[Text] from #CkTmpTest
         set @CkSql1='alter table dbo.area_mstr with nocheck add constraint
'+@CkName1 + ' Check ' + @CkContent1
         exec(@CkSql1)
         delete from #CkTmpTest where [Name] = @CkName1
      end
      drop table #CkTmpTest
   end
 --Add Unique
   if exists(select * from tempdb.dbo.sysobjects where name like '%#UQTmpTest%')
   begin
      declare @UQName nvarchar(50),@UqSql nvarchar(200)
  while exists(select * from #UQTmpTest)
      begin
       select top 1 @UQName=[Name] from #UQTmpTest
       set @UqSql='Alter table dbo.area_mstr with nocheck add CONSTRAINT ['+@UQName+'] UNIQUE  NONCLUSTERED ([name])'
       exec(@UqSql)
       delete from #UQTmpTest where [Name] = @UQName
  end
     drop table #UQTmpTest
   end
 --Add Index
 if exists(select * from tempdb.dbo.SysObjects where name like '%#IdxTmpTest%')
 begin
  declare @IdxName nvarchar(100), @colname nvarchar(100), @idxcol nvarchar(2000)
  set @idxcol = ''
  declare idx cursor for select distinct IdxName from #IdxTmpTest
  OPEN idx
  fetch next from idx into @IdxName 
  while @@fetch_status = 0
  begin
   declare col cursor for select colname from #IdxTmpTest where idxname = @idxname order by keyno
   open col
   fetch next from col into @colname 
   while @@fetch_status = 0
   begin
    set @idxcol = @idxcol + @colname + ','
    fetch next from col into @colname 
   end
   close col
   deallocate col  
   if right(ltrim(rtrim(@idxcol)),1)=','
    set @idxcol = substring(ltrim(rtrim(@idxcol)),1, len(ltrim(rtrim(@idxcol)))-1)
   exec('create index '+ @IdxName + ' on '+ @TableName + '(' + @idxcol + ')')
   set @idxcol=''
   fetch next from idx into @IdxName 
  end
  close idx
  deallocate idx  
  drop table #IdxTmpTest
 end

end
else
begin
    exec('Alter Table '+ @TableName +' Alter column '+ @FieldName +' '+ @FieldAttribute )
    if @@error != 0
    begin
        set @errMsg = N'表
'+@TableName+N'中字段'+@FieldName+N'结构修改失败! 请检查'
        goto errHandle
    end
end

if @def != ''
begin    
 exec('alter table '+ @TableName + ' add ' + @Def + ' For ' + @FieldName)
    if @@error != 0
    begin
        set @errMsg = N'表'+ @TableName + N'中字段
'+@FieldName+N'增加默认值失败! 请检查'
        goto errHandle
    end
end


set nocount off

commit tran
print @errMsg
return

errHandle:
    rollback tran
    print @errMsg


GO

相关文章推荐

更新所有指定表时间字段及增加一天存储过程

更新存储表采集时间为当前系统时间 create or replace procedure P_Update is v_sql varchar2(1000); begin declare...

SQL Server 学习笔记④ 查询某个数据所在数据库表中的哪个字段上出现过的存储过程

--2008查找某数据库中的列是否存在某个值 create proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarcha...

为表名不同但属性相同的多个表添加字段的mysql存储过程示例

/* 在hmp DB服务器上执行一下sql */ /* modify table [hmp_data_glucose_XXX] structure */ USE  bwfHmp; DROP PROCE...

SQL存储过程之删除指定表中指定字段所有约束

CREATE PROCEDURE dropconstraint @tb varchar(100),@fd varchar(100) AS declare @tbname sysname,@fdnam...
  • iowns
  • iowns
  • 2012-04-26 15:55
  • 224

在mysql上使用存储过程给相应的表加入字段

在mysql上使用存储过程给相应的表加入字段 程序升级需要给mysql的数据库加上特定字段以供升级需要 环境 mysql5.5 数据库名 thams 需要修改的数据库表名 %_document (...

Sql Server 不常见应用之一:获取表的基本信息、字段列表、存储过程参数列表

【推荐】Sql Server 不常见应用之一获取表的基本信息、字段列表、存储过程参数列表 ——通过知识共享树立个人品牌。    一、获取表的基本信息SELECT [TableName] = [Tabl...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)