表修改字段存储过程

转载 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

MySQL任务调度和存储过程实现实时修改表中字段值

1、创建表CREATE TABLE `zf_activity` ( `ACTIVITY_ID` int(11) NOT NULL AUTO_INCREMENT, `ZF_USER_ID` int(...
  • NFA_YY
  • NFA_YY
  • 2017年06月23日 09:38
  • 293

用存储过程进行添加与修改数据

--创建添加与修改的存储过程 --省   create --首先定义输入与输出参数 --例:@uID nvarchar(50)  输入   @i int output 输出 --然后定义临时存...
  • qq_27179087
  • qq_27179087
  • 2017年04月25日 11:34
  • 1435

MYSQL存储过程、游标和修改表字段优化方案

Mysql存储过程游标加循环 转自:http://blog.csdn.net/myron_sqh/article/details/15862923 Mysql的存储过程是从版本5才开始支持的,所...
  • mike_caoyong
  • mike_caoyong
  • 2015年03月23日 20:42
  • 1398

oracle存储过程将某张表一个字段的多值分开成多条数据插入到一张新表

create or replace PROCEDURE PRO_MANDATESCOPE (dataid in varchar2,tablename in varchar2 ) AS ...
  • qq_26676207
  • qq_26676207
  • 2016年06月30日 18:30
  • 2678

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

/* 在hmp DB服务器上执行一下sql */ /* modify table [hmp_data_glucose_XXX] structure */ USE  bwfHmp; DROP PROCE...
  • chinawangfei
  • chinawangfei
  • 2016年09月07日 15:44
  • 1334

oracle存储过程之数据增删查改及调用

存储过程: 存储过程是 SQL, PL/SQL, Java 语句的组合, 它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是,代码存储一次但是能够被多个程序使用。是存放在数据库服...
  • u011955534
  • u011955534
  • 2013年11月09日 14:38
  • 10526

Hive修改表语句

0x01:重命名表 1 ALTER TABLE table_name RENAME TO new_table_name; 上面这个...
  • xiaolang85
  • xiaolang85
  • 2014年03月20日 17:57
  • 37535

PostgreSQL修改数据库表的列属性(ALTER语句)

转自:http://blog.chinaunix.net/uid-15145533-id-2775847.html 我们可以用客户端的语句改写,psql 比如把数据库表journal里的keyw...
  • wujiang88
  • wujiang88
  • 2016年06月03日 14:56
  • 429

监控SQL:用触发器来监控是哪些语句、存储过程修改了特定表(4)

实际问题如下: 有一个表A,表A中有一个字段B被某个存储过程修改了。 比如B的初始值为1,被某个存储过程改为了0,但是现在存储过程大概有1000多个,有没有什么好的方法,可以监测到这个字段是被谁修...
  • yupeigu
  • yupeigu
  • 2016年12月16日 12:25
  • 794

Mysql中用SQL增加、删除字段,修改字段名、字段类型、注释,调整字段顺序总结

mysql
  • u012474716
  • u012474716
  • 2017年12月27日 14:06
  • 41
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:表修改字段存储过程
举报原因:
原因补充:

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