--exec usp_Table_AddField 'a', 'RIGHT2','char(1) default '''''
/*
Author: LB, Date : 2005-04-12
Desc : 用于增加表字段,包括已复制的表和未复制的表
*/
CREATE proc dbo.usp_Table_AddField
@TableName varchar(50),
@FieldName varchar(50),
@FieldAttribute varchar(200)
as
set nocount on
declare @errMsg nvarchar(400)
set @errMsg = N'表'+@TableName +N'字段'+@FieldName+N'增加成功!'
if not exists(select name from sysobjects where xtype = 'U' and name = @TableName)
begin
set @errMsg = N'表'+@TableName+ N'不存在, 请检查!'
print @errMsg
return
end
if exists(select l.name from sysobjects l inner join syscolumns r on l.id = r.id and l.name = @TableName and l.xtype ='U' and r.name = @FieldName)
begin
set @errMsg = N'表'+@TableName+N'中字段'+@FieldName+ N'已存在, 不能增加!'
print @errMsg
return
end
if CHARINDEX('CHAR', @FieldAttribute, 1) > 0
set @FieldAttribute = substring(@FieldAttribute,1, CHARINDEX(')',@FieldAttribute, 1)) +' COLLATE SQL_Latin1_General_CP1_CI_AS ' +
substring(@FieldAttribute, CHARINDEX(')',@FieldAttribute,1)+1, len(@FieldAttribute)-CHARINDEX(')',@FieldAttribute,1))
if exists(select * from sysobjects where xtype='U' and replinfo != 0 and name = @TableName)
exec sp_repladdcolumn @TableName, @FieldName, @FieldAttribute
else
exec('alter table '+@TableName + ' add '+ @FieldName +' '+@FieldAttribute)
if @@error != 0
set @errMsg = N'表'+@TableName +N'字段'+@FieldName+N'增加失败,请检查!'
print @errMsg
GO