sql server表结构处理备忘

--删除表字段默认约束

DECLARE @tablename VARCHAR(100), @columnname VARCHAR(100), @tab VARCHAR(100)
SET @tablename='TableName'
SET @columnname='FieldName'
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'
print @cmd
exec (@cmd)

---增加删除列

alter table TableName add FieldName Datatype Default....

alter table TableName drop column FieldName

--增加与删除表主键

alter table TableName add constraint primary key(Field1,Field2,Field3....)

alter table TableName drop constraint PK_Name

alter table TableName drop constraint DEFName