通过存储过程操作表

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

1.添加列
exec pro_AddColumn 表名，列名,''
参数表
@tableName varchar(100),
@Msg varchar(50) output

3.删除
exec pro_AddColumn 表名,''
参数表
@tableName varchar(100),
@Msg varchar(50) output
*/

--1.添加新表

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

create procedure pro_CreateTable
@tableName varchar(20),
@Msg varchar(50) output
as

declare @flag char(1)
set @flag=0
set @Msg='表添加失败!'

/*如果表不存在*/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tablename+']') and objectProperty(id, N'IsUserTable') = 1)
begin
/*创建默认表信息*/
exec('Create Table '+@tableName+'
   ( Fd_No nvarchar(15),
   Fd_XL         nvarchar(10)   ,
   Fd_Master     nvarchar(10),
   Fd_StdNo      nvarchar(3)           primary   key,
   Fd_StdName    nvarchar(20),
   Fd_Age        nvarchar(4)   ,
   Fd_Zoom       nvarchar(15)
   )')
set @flag=1
  set @Msg='创建表成功！'
end
else
begin
  set @flag=1
   set @Msg='表己存在！'
end
select @flag
return @flag

--2.删除表

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

create procedure pro_DropTable
@tableName varchar(20),
@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)
begin
/*创建默认表信息*/
exec('Drop Table '+@tableName)
set @flag=1
  set @Msg='删除表成功！'
end
else
begin
  set @flag=1
   set @Msg='表不存在！'
end
select @flag
return @flag