频道导航

• ▸ PHP
• ▸ Java
• ▸ Java SE
• ▸ Python
• ▸ C#
• ▸ C&C++
• ▸ Ruby
• ▸ VB
• ▸ asp.Net
• ▸ Go
• ▸ Perl
• ▸ netty
• ▸ Django
• ▸ Delphi
• ▸ Jsp
• ▸ .NET Core
• ▸ Spring
• ▸ Flask
• ▸ Springboot
• ▸ SpringMVC
• ▸ Lua
• ▸ Laravel
• ▸ Mybatis
• ▸ Asp
• ▸ Groovy
• ▸ ThinkPHP
• ▸ Yii
• ▸ swoole
• ▸ HTML
• ▸ HTML5
• ▸ JavaScript
• ▸ CSS
• ▸ jQuery
• ▸ Bootstrap
• ▸ Angularjs
• ▸ TypeScript
• ▸ Vue
• ▸ Dojo
• ▸ Json
• ▸ Electron
• ▸ Node.js
• ▸ extjs
• ▸ Express
• ▸ XML
• ▸ ES6
• ▸ Ajax
• ▸ Flash
• ▸ Unity
• ▸ React
• ▸ Flex
• ▸ Ant Design
• ▸ Web前端
• ▸ 微信小程序
• ▸ 微信公众号
• ▸ iOS
• ▸ Android
• ▸ Swift
• ▸ Hybrid
• ▸ Cocos2d-x
• ▸ Flutter
• ▸ Xcode
• ▸ Silverlight
• ▸ cocoa
• ▸ Cordova

• 前端之家
• MsSQL
• sqlserver对字段的添加修改删除、以及字段的说明

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