表删除字段存储过程

原创 2007年09月28日 15:12:00


--------------------------------------------------------------------------------
-- FILE                : dbo.usp_Table_DeleteField.sql
-- PRIMARY OBJECT      : dbo.usp_Table_DeleteField (Procedure)
-- CREATED BY/DATE     : Rapid SQL on 2005-04-20 13:05:44.103
-- SOURCE              : Reverse-Engineered from SKT225.TAPCS (SQL Server 08.00.0760)
--------------------------------------------------------------------------------
--exec usp_Table_DeleteField  'menu_det', 'RIGHTTYPE1'

/*
Author: LB, Date : 2005-04-12
Desc  : 用于删除表字段,包括已复制的表和未复制的表 
*/

CREATE proc dbo.usp_Table_DeleteField
@TableName varchar(50),
@FieldName varchar(50)
as
set nocount on
declare @errMsg nvarchar(400)
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 from sysobjects l inner join syscolumns r on l.id = r.id and l.name = @TableName and l.xtype ='U' and r.name = @FieldName)
begin
    set @errMsg = N'表
'+@TableName+N'中字段'+@FieldName
+ N'不存在, 不能删除!'
    print @errMsg
    return
end

begin tran

--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
    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 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+']]%'
        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
    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
        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
--Drop index
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 )
begin
    while 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 )
    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
  
        set @IdxSql0='drop index '+ @TableName
+'.'+@IdxName0
        exec(@IdxSql0)
        if @@error != 0
        begin
            set @errMsg = N'删除表
'+@TableName+N'索引'+@IdxName0+N'
失败,请检查!'
              goto errHandle
         end
    end
end


if exists(select * from sysobjects where xtype='U' and replinfo != 0 and name = @TableName)
    exec sp_repldropcolumn @TableName , @FieldName
else
    exec('alter table
'+@TableName
+ ' drop column '+ @FieldName )
if @@error != 0
begin
    set @errMsg = N'表
'+@TableName +N'字段'+@FieldName+N'
删除失败,请检查!'
    goto errHandle
end

commit tran
print @errMsg
return


errHandle:
    rollback tran
    print @errMsg

GO
 

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

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

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

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

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
  • 221

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

create or replace PROCEDURE PRO_MANDATESCOPE (dataid in varchar2,tablename in varchar2 ) AS ...

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

【推荐】Sql Server 不常见应用之一获取表的基本信息、字段列表、存储过程参数列表 ——通过知识共享树立个人品牌。    一、获取表的基本信息SELECT [TableName] = [Tabl...

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

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

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

/* 在hmp DB服务器上执行一下sql */ /* modify table [hmp_data_glucose_XXX] structure */ USE  bwfHmp; DROP PROCE...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:表删除字段存储过程
举报原因:
原因补充:

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