sqlserver对字段的添加修改删除、以及字段的说明

228 阅读 0 评论 151 点赞

我是靠谱客的博主甜蜜巨人，最近开发中收集的这篇文章主要介绍sqlserver对字段的添加修改删除、以及字段的说明，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

复制代码代码如下:

 
--新增表字段 
ALTER procedure [dbo].[sp_Web_TableFiled_Insert] 
( 
@TableName varchar(100), 
@FieldName varchar(100), 
@FieldExplain varchar(200), 
@DataType varchar(100), 
@ConnectTableName varchar(100), 
@FieldLength int, 
@NewsID int output 
) 
as 
begin transaction mytran 
declare @errorSum int 
if not exists (SELECT * FROM syscolumns where id=object_id(@TableName) AND name=@FieldName) 
begin 
insert tb_TableField 
( 
TableName, 
FieldName, 
FieldExplain, 
DataType, 
ConnectTableName, 
FieldLength, 
UserSetSign 
) 
values 
( 
@TableName, 
@FieldName, 
@FieldExplain, 
@DataType, 
@ConnectTableName, 
@FieldLength, 
'1' 
) 
declare @sql varchar(8000) 
--判断类型 
if(@DataType='decimal') 
begin 
set @sql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType +'(' +Convert(varchar,@FieldLength)+',2'+')' 
end 
else if(@DataType='varchar') 
begin 
set @sql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType +'(' +Convert(varchar,@FieldLength)+')' 
end 
else 
begin 
set @sql = 'alter table ' + @TableName +' add ' + @FieldName +' ' + @DataType 
end 
exec(@sql) 
EXECUTE sp_addextendedproperty N'MS_Description', @FieldExplain, N'user', N'dbo', N'Table', @TableName, N'column' , @FieldName; 
set @errorSum=@errorSum+@@error 
set @NewsID=0; 
end 
else 
begin 
set @NewsID=1; 
end 
if(@errorSum>0) 
begin 
rollback tran 
end 
else 
begin 
commit tran mytran 
end 
--修改表字段 
ALTER procedure [dbo].[sp_Web_TableFiled_Update] 
( 
@TableName varchar(100), 
@FieldName varchar(100), 
@FieldExplain varchar(200), 
@DataType varchar(100), 
@ConnectTableName varchar(100), 
@FieldLength int, 
@ID int, 
@NewsID int output 
) 
as 
begin transaction mytran 
declare @fname varchar(100) 
declare @errorSum int 
--先取出表中以前的字段名称 
select @fname=FieldName from tb_TableField where ID=@ID 
declare @pstid int 
declare @sql varchar(8000) 
--再根据字段名称取出tb_PaySystemToLocation中对应的ID 
select @pstid=ID from tb_PaySystemToLocation where LocationField=@fname 
set @sql = 'sp_rename '+CHAR(39)+@TableName+'.['+@fname+']'+CHAR(39)+',' +char(39)+@FieldName+char(39)+',' + char(39)+'COLUMN' +CHAR(39) 
exec(@sql) 
update tb_TableField 
set TableName=@TableName, 
FieldName=@FieldName, 
FieldExplain=@FieldExplain, 
DataType=@DataType, 
ConnectTableName=@ConnectTableName, 
FieldLength=@FieldLength 
where ID=@ID 
--修改字段说明 
EXECUTE sp_updateextendedproperty N'MS_Description', @FieldExplain, N'user', N'dbo', N'Table', @TableName, N'column' , @FieldName; 
--EXEC sp_updateextendedproperty 'MS_Description',@FieldExplain,'user',dbo,'table',@TableName,'column',@FieldName 
set @NewsID=0; 
set @errorSum=@errorSum+@@error 
if(@@ERROR>0) 
begin 
rollback tran 
end 
else 
begin 
commit tran mytran 
end 
-删除表字段 
ALTER procedure [dbo].[sp_Web_TableFiled_Delete] 
( 
@ID int, 
@NewsID int output 
) 
as 
begin transaction mytran 
declare @fname varchar(100) 
declare @tablename varchar(100) 
declare @pstid int 
declare @sql varchar(8000) 
declare @errorSum int 
--取出字段名,表名 
select @fname=FieldName,@tablename=TableName from tb_TableField where ID=@ID 
--取出tb_PaySystemToLocation的ID 
select @pstid=ID from tb_PaySystemToLocation where LocationField=@fname 
delete from tb_TableField where ID=@ID 
set @sql='ALTER TABLE ' +@tablename+ ' DROP COLUMN '+ @fname 
exec(@sql) 
set @errorSum=@errorSum+@@error 
set @NewsID=0; 
if(@errorSum>0) 
begin 
rollback tran 
end 
else 
begin 
commit tran mytran 
end 