通过存储过程动态操作表中的列代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】通过存储过程动态操作表中的列
作者:天玺 / 发布于2016/2/1/ 990
动态操作表
--------------------------
/*使用方法

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
试试其它关键字
　存储过程　
同语言下
可能有用的
天玺贡献的其它代码(10)