怎样取得该字段的所有关系、索引/键或者约束,然后把他们删除,再把该字段删除?

如果某字段存在关系、索引/键或者约束时,删除该字段就会出错,请问怎样取得该字段的所有关系、索引/键或者约束,然后把他们删除,再把该字段删除?在线等待,很急!!!!
---------------------------------------------------------------

--删除某字段的所有关系

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='表名'
and a.name='字段名'
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='表名'
and d.name='字段名'
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='表名'
and d.name='字段名'

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

--再删除字段
alter table 表名 drop column 字段名

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus