打算删除tbl_1表的col_1列,但如果此列设置有默认值的话
直接ALTER TABLE tbl_1 DROP COLUMN col_1会提示有对象存在,而无法删除.
需要先删除对应的默认值,然后删除列,具体如下:
declare @sss varchar(100);
select @sss=name from sysobjects where xtype='D' and id=
(select b.cdefault from sysobjects a,syscolumns b where a.id=b.id and a.name='tbl_1' and b.name='col_1')
--print @sss
if @sss<>''
begin
exec('alter table tbl_1 drop constraint ' + @sss)
end
go
if exists(select * from sysobjects where name='tbl_1' and xtype='U')
begin
if exists(select a.* from sysobjects a,syscolumns b
where a.id=b.id and a.name='tbl_1'
and a.xtype='U' and b.name='col_1')
ALTER TABLE tbl_1 DROP COLUMN col_1
end
go
直接ALTER TABLE tbl_1 DROP COLUMN col_1会提示有对象存在,而无法删除.
需要先删除对应的默认值,然后删除列,具体如下:
declare @sss varchar(100);
select @sss=name from sysobjects where xtype='D' and id=
(select b.cdefault from sysobjects a,syscolumns b where a.id=b.id and a.name='tbl_1' and b.name='col_1')
--print @sss
if @sss<>''
begin
exec('alter table tbl_1 drop constraint ' + @sss)
end
go
if exists(select * from sysobjects where name='tbl_1' and xtype='U')
begin
if exists(select a.* from sysobjects a,syscolumns b
where a.id=b.id and a.name='tbl_1'
and a.xtype='U' and b.name='col_1')
ALTER TABLE tbl_1 DROP COLUMN col_1
end
go
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29512902/viewspace-1151665/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29512902/viewspace-1151665/