SQL: 删除带约束的列

最新推荐文章于 2020-12-06 14:16:36 发布

iteye_20954

最新推荐文章于 2020-12-06 14:16:36 发布

阅读量334

点赞数

文章标签：数据库

-- this script drops the default constraint which is generated by the setting of default value. DECLARE @tablename VARCHAR(100), @columnname VARCHAR(100), @tab VARCHAR(100) SET @tablename='CountryGroupEmailAndWaitAux' SET @columnname='actionOfHasNoValidEmail' declare @defname varchar(100) declare @cmd varchar(100) select @defname = name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = @tablename AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id(@tablename) AND name = @columnname) select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname if @cmd is null print 'No default constraint to drop' exec (@cmd)

在删除对应的默认约束后，执行：

ALTER TABLE table1 DROP COLUMN column1

即可删除字段。