(转帖) --删除某字段的所有关系declare @tbname sysname,@fdname sysname select @tbname='t' --要处理的表名 ,@fdname='a' --要处理的字段名--declare tb cursor local for --默认值约束 select sql='alter table ['+b.name+'] drop constraint ['+d.name+']' from syscolumns a join sysobjects b on a.id=b.id join syscomments c on a.cdefault=c.id join sysobjects d on c.id=d.id where b.name=@tbname and a.name=@fdname union all --外键引用 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 join sysobjects e on e.id=a.rkeyid join syscolumns f on f.id=e.id and a.rkey=f.colid where e.name=@tbname and d.name=@fdname union all --索引 select case e.xtype when 'PK' 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<>'dtproperties' join syscolumns d on b.id=d.id and b.colid=d.colid join sysobjects e on c.id=e.parent_obj where a.indid not in(0,255) and c.name=@tbname and d.name=@fdnamedeclare @s varchar(8000) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb
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
--删除某字段的所有关系declare @tbname sysname,@fdname sysname
select @tbname='t' --要处理的表名
,@fdname='a' --要处理的字段名--declare tb cursor local for
--默认值约束
select sql='alter table ['+b.name+'] drop constraint ['+d.name+']'
from syscolumns a
join sysobjects b on a.id=b.id
join syscomments c on a.cdefault=c.id
join sysobjects d on c.id=d.id
where b.name=@tbname
and a.name=@fdname
union all --外键引用
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
join sysobjects e on e.id=a.rkeyid
join syscolumns f on f.id=e.id and a.rkey=f.colid
where e.name=@tbname
and d.name=@fdname
union all --索引
select case e.xtype when 'PK' 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<>'dtproperties'
join syscolumns d on b.id=d.id and b.colid=d.colid
join sysobjects e on c.id=e.parent_obj
where a.indid not in(0,255)
and c.name=@tbname
and d.name=@fdnamedeclare @s varchar(8000)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
to:wzjcntlqs(是做生意的家伙)
我说得是程序里面实现,
例如有个按钮“删除字段_notnull2”,用户单击这个按钮,字段_notnull2就删除了。
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
MS VIP风范~