--------------------------
/*使用方法
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