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

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

 
--新增表字段 
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',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),@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',@FieldName; 
--EXEC sp_updateextendedproperty 'MS_Description','user',dbo,'table','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 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!