declare @tablename varchar(100), @columnname varchar(100), @tab varchar(100)
set @tablename = 'T_Core_Department' --表名
set @columnname= 'Dpt_Type' --字段名称
declare @defname varchar(100) --约束名称
declare @cmd varchar(100) --构造的SQL语句
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)
GO
set @tablename = 'T_Core_Department' --表名
set @columnname= 'Dpt_Type' --字段名称
declare @defname varchar(100) --约束名称
declare @cmd varchar(100) --构造的SQL语句
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)
GO