打算删除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

ITPub博客

SQL Server语句删除带有默认值的字段

最新文章