MSSQL-DDL

常用数据库定义语言DDL

--1.判断表中是否存在某个字段
select * from 
SysObjects a left join SysColumns b on a.id = b.id 
where a.name = 'TableName' and b.Name='ColumnName'

--2.在表中新增一个字段
alter table TableName add ColumnName varchar(100) default ('') with values

--3.在表中删除一个字段
alter table TableName drop column ColumnName

--4.修改表中字段类型
alter table TableName alter column ColumnName new_data_type

--5.查询某个字段的默认值
select b.name from syscolumns a,sysobjects b where a.id=object_id('TableName') 
and b.id=a.cdefault and a.name='IsCanceled' and b.xtype='D'
if @defaultName is not null

--6.删除默认值
alter table TableName drop constraint DefaultName