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發布的刪除處理中的錯誤,同時改原來的處理語句為通用存儲過程

--鄒建 2004.07(引用請保留此信息)--*/

/*--調用示例
	
	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