通过存储过程动态操作表中的列

--------------------------
/*使用方法

1.添加列
 exec pro_AddColumn 表名，列名，类型/属性,''
 参数表
 @tableName varchar(100),
 @columNname varchar(100),
 @DbType varchar(32),
 @Msg varchar(50) output
2.修改 
 exec pro_AddColumn 表名，列名，类型/属性,''
 参数表
 @tableName varchar(100),
 @columNname varchar(100),
 @DbType varchar(32),
 @Msg varchar(50) output
 
3.删除
 exec pro_AddColumn 表名，列名
 参数表
 @tableName varchar(100),
 @columNname varchar(100),
 @Msg varchar(50) output
*/
----------------------------
--1、添加存储过程

if exists(select name from sysobjects where name='pro_AddColumn' and type='p')
drop procedure pro_AddColumn
go
create procedure pro_AddColumn
@tableName varchar(100),@columNname varchar(100),@DbType varchar(32),@Msg varchar(50) output
as
declare @flag char(1)
set @flag=0
set @Msg='列添加失败！'
/*
向指定表添加列
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)

if (NOT exists ( select * from dbo.syscolumns where name =@columnname and id in
(select id from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1))
)
begin
  -- select 'not exist,now is adding...' 不存在,则添加
  declare @cmdAddColumn varchar(100)
  select  @cmdAddColumn='alter TABLE '+ @tablename+' ADD '+@columnname+' '+@DbType
  exec(@cmdAddColumn)
  set  @flag=1
  set @Msg='列添加成功！'
end
else
 begin
  -- select 'Existed ！now is Deleteing......'
  -- exec pro_DropColumn @tablename,@columnname
  set  @flag=1
  set @Msg='列己存在！'
 end
select @flag
return @flag

--2、删除存储过程

if exists(select name from sysobjects where name='pro_DropColumn' and type='p')
drop procedure pro_DropColumn
go

create procedure pro_DropColumn
@tableName varchar(100),@columNname varchar(100),@Msg varchar(50) output
as
/*
删除指定表指定列
*/
begin
declare @flag char(1)

set @flag=0
set @Msg='列删除失败！'
-------------------------------删除过程开始------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)

if (exists ( select * from dbo.syscolumns where name =@columnname and id in
(select id from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1))
)
begin  --如果存在此表和此列
--------------------------------删除约束开始-----------------------------------------------------
declare @tab varchar(100)
declare @defname varchar(100)
declare @cmd varchar(100)
declare @cmdDel varchar(100)
select @defname = name
FROM sysobjects so
JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id(@tablename) AND
name = @columnname)

select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname
if @cmd is null print 'No default constraint to drop'   --删除己存在的约束
exec (@cmd)
-------------------------------删除约束结束-----------------------------------------------------------

-------------------------------删除指定列开始-----------------------------------------------------------
select @cmdDel='alter table '+@tablename+' drop column '+ @columnname
if @cmdDel is null
begin
  print '列删除失败'
  set @flag=0
  set @Msg='列删除失败！'
end
begin
 exec (@cmdDel)
 set @flag=1
 set @Msg='列删除成功！'
end
-------------------------------删除指定列结束------------------------------------------------------
end
-------------------------------删除过程结束----------------------------------------------------
else --不存在些列，不用删除
 begin
   set @flag=1
   set @Msg='指定列不存在！'
 end

-------------------------------------------------------------------------------------
select @flag
return @flag
end
GO

--3、修改存储过程

if exists(select name from sysobjects where name='pro_AlterColumn' and type='p')
drop procedure pro_AlterColumn
go
create procedure pro_AlterColumn
@tableName varchar(100),@columNname varchar(100),@DbType varchar(32),@Msg varchar(50) output
as
declare @flag char(1)
set @flag=0
set @Msg='列添加失败！'
/*
  查找表中的列
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)

if ( exists ( select * from dbo.syscolumns where name =@columnname and id in
(select id from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1))
)
begin
  -- select 'Existsed ,now is alterring...' 修改
  declare @cmdAddColumn varchar(100)
  select  @cmdAddColumn='alter TABLE '+ @tablename+' alter column '+@columnname+' '+@DbType
  exec(@cmdAddColumn)
  set  @flag=1
  set @Msg='修改成功！'
end
else
 begin
  -- select 'not existed ！' 不存在要修改的列
  set  @flag=1
  set @Msg='列己不存在！'
 end
select @flag
return @flag