259.删除某字段的所有关系——存储过程

超、凢脫俗 2023-12-27 06:38 64阅读 0赞

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_deletefield]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[p_deletefield]
    GO
    
    /*--删除某字段的所有关系及该字段
    
    	删除某字段时,常常因为在该字段上定义了约束/外键/默认值
    	导致字段删除失败,此存储过就是删除表中指定字段的这些关系
    
    	修正2004.05发布的删除处理中的错误,同时改原来的处理语句为通用存储过程
    
    */
    
    /*--调用示例
    	
    	exec p_deletefield '表名','字段名'
    --*/
    create proc p_deletefield
    @tbname sysname,	--要处理的表名
    @fdname sysname,	--要处理的字段名
    @delfield bit=1		--0只删除关系,1同时删除字段
    as
    declare hCForEach cursor global for
    --默认值约束
    select sql='alter table ['+b.name+'] drop constraint ['+d.name+']'
    from syscolumns a
    	join sysobjects b on a.id=b.id and a.name=@fdname and b.name=@tbname 
    	join syscomments c on a.cdefault=c.id
    	join sysobjects d on c.id=d.id
    union	--外键引用
    select s='alter table ['+c.name+'] drop constraint ['+b.name+']'
    from sysforeignkeys a
    	join sysobjects b on b.id=a.constid
    	join sysobjects c on c.id=a.fkeyid
    	join syscolumns d on d.id=c.id and a.fkey=d.colid and d.name=@fdname
    	join sysobjects e on e.id=a.rkeyid and e.name=@tbname
    	join syscolumns f on f.id=e.id and a.rkey=f.colid 
    union	--主键/唯一键/索引
    select case when e.xtype in('PK','UQ') then 'alter table ['+c.name+'] drop constraint ['+e.name+']'
    		else 'drop index ['+c.name+'].['+a.name+']' end
    from sysindexes a
    	join sysindexkeys b on a.id=b.id and a.indid=b.indid
    	join sysobjects c on b.id=c.id and c.xtype='U' and c.name=@tbname
    	join syscolumns d on b.id=d.id and b.colid=d.colid and d.name=@fdname
    	left join sysobjects e on e.id=object_id(a.name)
    where a.indid not in(0,255)
    
    exec sp_msforeach_worker '?'
    
    if @delfield=1
    	exec('alter table ['+@tbname+'] drop column ['+@fdname+']')
    go