1.表字段添加
格式:ALTER TABLEtable_nameADDcolumn_name data_type
设置默认值:
ALTERTABLEtable_nameADDCOLUMNcolumn_nameVARCHAR(10)NOTNULLWITHDEFAULT'02'
MSSQL常用收集
Int IDENTITY (1, 1) NOT NULL PRIMARY KEY 自动编号
Int Default 0 数字
DateTime Default GetDate() 日期
Varchar(50) 文本
LongText 备注
以下是存储过程:
ALTER proc [dbo].[_table_column_add]@table varchar(100),--表名@columns varchar(100),--字段名@type varchar(100)='' --数据类型(如不设置,默认为varchar(100))asdeclare @sql varchar(3000),@type_code varchar(100)if len(@type)<1 set @type=''if @type='' or @type=NULLbeginset @type_code='varchar(100)'endset @sql='if NOT EXISTS (select a.Name as columnName from syscolumns as a , sysobjects as b where a.ID=b.ID and b.Name='''+@table+'''and a.name='''+@columns+''')BEGINalter table '+@table+' add '+@columns+' '+@type_code+'select a.Name as columnName from syscolumns as a , sysobjects as b where a.ID=b.ID and b.Name='''+@table+'''ENDELSEBEGINPRINT ''字段已经存在!''END'--print @sqlexec(@sql)
2.表字段修改
格式:ALTER TABLEtable_nameALTERCOLUMN column_name new_data_type
ALTER TABLEtable_nameMODIFYcolumn_name new_data_type(MYSQL中写法)
3.表字段删除
格式:ALTER TABLEtable_nameDROPCOLUMN column_name