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