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 NMS_Description, @FieldExplain, Nuser, Ndbo, NTable, @TableName, Ncolumn , @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 NMS_Description, @FieldExplain, Nuser, Ndbo, NTable, @TableName, Ncolumn , @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