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

复制代码 代码如下: --新增表字段 ALTER procedure [dbo].[sp_Web_TableFiled_Insert] ( @TableName varchar(100), @FieldName varchar(100), @FieldExplain varchar(200), @DataType varchar(100), @ConnectTableName varchar(100), @FieldLeng

复制代码 代码如下: --新增表字段 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