SQL 增删改列

-------------------------------------------------------------------/*添加表中的列*/

begin try

declare @tableName varchar(100),

@tableColumn varchar(2000),

@columnName varchar(2000)

set @tableName='你的表名'

set @tableColumn='列结构'-----例如'CreateTime datetime null default(getdate()),CloseTime datetime'

set @columnName='列名'-----用于判断是否存在

if not exists(

select * from sys.objects a,sys.columns b 

where b.object_id=a.object_id 

and  a.name=@tableName --表名

and   b.name=@columnName--列名

)

begin---------''为转义字符，即‘

exec('alter table '+@tableName+' add '+@tableColumn)

exec('sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''你的列说明'' , @level0type=N''SCHEMA'',@level0name=N''dbo'',@level1type=N''TABLE'',@level1name=N'''+@tablename+''', @level2type=N''COLUMN'',@level2name=N'''+@columnName+'''')

exec('sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''你的列说明'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@tablename+''', @level2type=N''COLUMN'',@level2name=N''你的列名''')

print '在表'+@tableName+'添加列'+@columnName+'成功'

end

end try

begin catch

print ERROR_MESSAGE()

end catch

Go---

--------------------------------------------------------------------------------------

 

-----------------------------------------------------------------------/*修改表中的列*/

begin try

declare @tableName varchar(100),

@tableColumn varchar(100),

@columnType varchar(100) 

set @tableName='你对应的表'

set @tableColumn='你要修改的列'

set @columnType='数据类型'

if exists(

select * from sys.objects a,sys.columns b 

where b.object_id=a.object_id 

and  a.name=@tableName --表名

and   b.name=@tableColumn--列名

)

begin

exec('alter table '+@tableName+' alter column '+@tableColumn+' '+@columnType)

print '在表'+@tableName+'修改列'+@tableColumn+'成功'

end

end try

begin catch

print ERROR_MESSAGE()

end catch

Go---

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------/*修改表中的列名*/

begin try

declare @tableName varchar(100),

@tableColumn varchar(100),

@tableNewColumn varchar(100) 

set @tableName='你的表名'

set @tableColumn='你老的列名'

set @tableNewColumn='你新的列名'

if exists(

select * from sys.objects a,sys.columns b 

where b.object_id=a.object_id 

and  a.name=@tableName --表名

and   b.name=@tableColumn--列名

)

begin

EXEC   sp_rename   '你的老列名',   '你新的列名',   'COLUMN'  ------建议带上 表名.列名

print '在表'+@tableName+'修改列'+@tableColumn+'成功'

end

else print @tableColumn+'不存在'

end try

begin catch

print ERROR_MESSAGE()

end catch

Go---

-----------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------/*删除表中的列*/

begin try

begin

declare @tableName varchar(100),

@tableColumn varchar(100) 

set @tableName='对应的表'

set @tableColumn='要删除的列'

if exists(

select * from sys.objects a,sys.columns b 

where b.object_id=a.object_id 

and  a.name=@tableName --表名

and   b.name=@tableColumn--列名

)

exec('alter table '+@tableName+' drop column '+@tableColumn)

print '在表'+@tableName+'修改列'+@tableColumn+'成功'

end

end try

begin catch

print ERROR_MESSAGE()

end catch

Go---

---------------------------------------------------------------------------------------------------------