表修改字段存储过程

最新推荐文章于 2021-01-19 14:32:16 发布
最新推荐文章于 2021-01-19 14:32:16 发布
阅读量933 收藏
点赞数
分类专栏： Delphi技术专题 文章标签： 存储 join table object delete insert

－-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

jing12
关注
专栏目录
使用存储过程批量修改mysql库表字段的字符集和排序规则
千里小马回忆录
06-11 779
mysql库的字符集和排序规则排序后对于已有的表不生效，只对新创建的表生效； 修改表的字符集和排序规则可以对字段生效。所以，只需要找出当前库所有的表，迭代修改表的即可解决问题。库的单独修改。 # 1、修改库database_name的字符集为utf8mb4，排序规则为utf8mb4_general_ci ### 连接mysql后，不用选择库，把database_name替换成目标库名字，直接执行这一句脚本。 ALTER DATABASE database_name CHARACTER SET..
存储过程批量修改表中字段值
N
11-22 1824
 处用存储过程批量修改表中字段值,在线视频:http://oracle2010.china-vhost.com/new/cursor/
存储过程之新增或者修改字段
lixinkuan的博客
03-06 3384
1、判断字段不存在则增加该字段 Talk is cheap， show me your code！ -- [新增]判断字段不存在则增加该字段 drop procedure if exists proc_add_column_unless_exist; delimiter $$ create procedure proc_add_column_unless_exist(in var_table...
MySQL任务调度和存储过程实现实时修改表中字段值
NFA_YY的博客
06-23 1353
1、创建表CREATE TABLE `zf_activity` ( `ACTIVITY_ID` int(11) NOT NULL AUTO_INCREMENT, `ZF_USER_ID` int(11) DEFAULT NULL COMMENT '活动发起人|2|2|1|1', `AGENCY_ID` int(11) DEFAULT NULL COMMENT '单位ID|2|2|1|1',
mysql 存储过程 修改表结构_mysql 修改表结构 判断并添加column 的 简单存储过程...
weixin_35839384的博客
01-19 172
处理思路：1、查找表结构，判断要加入的列是否已存在2、如果不存在，则执行添加CREATE PROCEDURE `mysql_sp_add_column`(IN p_DBName varchar(64) --数据库名, IN p_TableName varchar(100) --表名, IN p_ColumnName varchar(100) --字段名, IN p_Colu...
动态给表添加删除字段并同时修改它的插入更新存储过程
12-15
本文将深入探讨如何在SQL Server环境中动态地为表添加或删除字段，并同时修改与这些字段相关的插入和更新存储过程。 首先，我们需要创建一个辅助表[A]，它有两个字段：[ID]（自增长）和[FieldName]。表[A]的设计...
Oracle如何批量将表中字段名全转换为大写(利用简单存储过程)
01-19
搜到一个批量修改字段名为大写的存储过程，如下： 登陆到要修改的用户下， 执行： begin for cl in (SELECT table_name,column_name from user_tab_columns WHERE column_name<>upper(column_name) and upper...
SqlServer快速检索某个字段在哪些存储过程中(sql 语句)
09-09
这可以帮助数据库管理员定位到具体使用该字段的存储过程，以便进行进一步的分析或修改。 需要注意的是，由于`syscomments`视图的限制，如果存储过程的定义过长，可能会被分割成多行，这可能会影响搜索结果的准确性...
详解Oracle修改字段类型方法总结
12-16
本文主要针对Oracle修改字段类型的方法进行详细讲解，特别是在面对不同情况时如何有效地进行操作。 首先，我们来看一个具体的例子，假设有一个名为`tb`的表，其中包含一个名为`name`的字段，其数据类型为`nchar(20)...
为表添加和删除字段的存储过程
记事本
09-07 2071
SQLserver:FuncADDColumn(为表添加字段)。用于为表添加字段。可以侦测表中有没有这字段。如果没有，添加该字段。/*author:nybtime :2003/12/29fixtime :aim   :看表中是否有这个字段，如果没有，添加上input : 调用：execute FuncADDColumn ’a’,’c’,’int’execute Fu
使用存储过程批量修改表结构
与君共对清风月
07-19 1320
在数据库中有结构相同的若干表，tablename 各个表之间表名的规律为 tablename_0…9，如果需要修改表结构，添加一列，则可以使用存储结构运行下列语句： CREATE PROCEDURE alter_table_enegine() BEGIN DECLARE `@i` int(11); DECLARE `@sqlstr` VARCHAR(2048); SET `@i` = 0 ; ...
Oracle简单的存储过程+根据条件修改字段值
june1994的博客
08-25 2319
1、decode用法:decode（条件，条件值1，返回值1，条件值2，返回值2） BEGIN UPDATE relationship SET current = DECODE(rel_type,1,'is employee of', 4,'primary contact for',
sql 修改字段中部分值
hellotomo的博客
12-21 5271
replace函数 替换字段部分值
如何写好数据库存储过程
梓洋的博客
03-14 3179
首先需要判断一下是否存在存储过程 if exists (select * from sys.procedures where name='存储过程名字') --判断是否存在存储过程 drop proc 存储过程名字 --删除存储过程 go create proc 存储过程名字([@参数 数据类型 ]) --创建一个存储过...
存储过程（五）批量改变字段类型
u012045045的博客
05-18 560
create or replace procedure REPAIR_DIELD_CLASS is vSql varchar2(500); vTable varchar2(50); begin for m in 97..99 loop /* vTable := 'USER_CHANNEL_INFO_HISTORY_'||m;*/ vTa...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值