刪除表字段及其關系的存儲過程腳本
由於項目中需要動態刪除表字段及其關系, 特將其分享.
注意: 使用Drop刪除字段前必須先設法把其關系刪除才可以的;
CREATE PROCEDURE [dbo].[spDeleteColumnConstraint]
(
@tablename nvarchar(50),
@columnname nvarchar(50)
)
AS
--刪除某字段的所有關系
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 = @tablename
and a.name = @columnname
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=afkeyid
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 = @tablename
and d.name = @columnname
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=‘a‘
and d.name=‘id‘
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
DECLARE @SQL AS nvarchar(500)
set @SQL = ‘Alter table ‘ + @tablename + ‘ Drop Column ‘ + @columnname
EXECUTE sp_executesql @SQL
GO
var alimama_pid="mm_10096109_566393_892989"; var alimama_titlecolor="0000FF"; var alimama_descolor ="000000"; var alimama_bgcolor="FFFFFF"; var alimama_bordercolor="E6E6E6"; var alimama_linkcolor="008000"; var alimama_bottomcolor="FFFFFF"; var alimama_anglesize="0"; var alimama_bgpic="0"; var alimama_icon="0"; var alimama_sizecode="11"; var alimama_width=760; var alimama_height=90; var alimama_type=2;
google_ad_clIEnt = "pub-6430022987645146";google_ad_slot = "7812729674";google_ad_width = 728;google_ad_height = 90;