删除默认约束存储过程及增加、修改、删除字段语法

参考用：

 

---创建删除默认约束的存储过程（以前网上找的，不知道具体出处）
if exists (select * from sysObjects where name='Pro_DropColumnDFConstraint' and xtype='P')
 drop proc Pro_DropColumnDFConstraint
go
create proc Pro_DropColumnDFConstraint
 @tablename VARCHAR(100),
 @columnname VARCHAR(100)
AS
 declare @tab VARCHAR(100)
 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)
go

 

----删除Plans_ID列及默认约束
exec Pro_DropColumnDFConstraint 'Property','Plans_ID'
ALTER TABLE Property DROP COLUMN Plans_ID

--为表PlanProperty添加列PP_IsDisplay
alter table PlanProperty add PP_IsDisplay tinyint default 0 not null

--修改列

alter table PlanProperty alter column PP_IsDisplay int