复制代码 代码如下:

--新增表字段

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

【sqlserver对字段的添加修改删除、以及字段的说明】相关文章：

★ sqlserver2005 xml字段的读写操作

★ SqlServer查询表中各列名称、表中列数

★ mssql server .ldf和.mdf的文件附加数据库的sql语句

★ sql server 2005数据库备份还原图文教程

★ Microsoft Sql server2005的安装步骤图文详解及常见问题解决方案

★ sqlserver 中一些常看的指标和清除缓存的方法

★ mssql 指定字段编号sql语句

★ sqlserver子查询报错

★ 在SQL Server中修改字段类型和字段名称

★ sqlserver2005 安装图解教程以及SQL 2005 SP3补丁安装图文教程