表删除字段存储过程

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