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