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