create proc addcolumn @tablename varchar(30), --表名 @colname varchar(30), --要加的列名 @coltype varchar(100), --要加的列类型 @colid int --加到第几列 asdeclare @colid_max int declare @sql varchar(1000) --动态sql语句 -------------------------------------------------- if not exists(select 1 from sysobjects where name = @tablename and xtype = 'u ') begin raiserror 20001 '没有这个表 ' return -1 end -------------------------------------------------- if exists(select 1 from syscolumns where id = object_id(@tablename) and name = @colname) begin raiserror 20002 '这个表已经有这个列了! ' return -1 end--保证该表的colid是连续的 select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)if @colid > @colid_max or @colid < 1 set @colid = @colid + 1 -------------------------------------------------- set @sql = 'alter table '+@tablename+ ' add '+@colname+ ' '+@coltype exec(@sql)select @colid_max = colid from syscolumns where id = object_id(@tablename) and name = @colname if @@rowcount <> 1 begin raiserror 20003 '加一个新列不成功,请检查你的列类型是否正确 ' return -1 end -------------------------------------------------- --打开修改系统表的开关 EXEC sp_configure 'allow updates ',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1 set @sql = 'update syscolumns set colid = -1 where id = object_id( ' ' '+@tablename+ ' ' ') and colid = '+cast(@colid_max as varchar(10)) exec(@sql)--将其他列的列号加1 set @sql = 'update syscolumns set colid = colid + 1 where id = object_id( ' ' '+@tablename+ ' ' ') and colid > = '+cast(@colid as varchar(10)) exec(@sql)--将新列列号复位 set @sql = 'update syscolumns set colid = '+cast(@colid as varchar(10))+ ' where id = object_id( ' ' '+@tablename+ ' ' ') and name = ' ' '+@colname + ' ' ' ' exec(@sql) -------------------------------------------------- --关闭修改系统表的开关 EXEC sp_configure 'allow updates ',0 RECONFIGURE WITH OVERRIDE go调用方法: exec addcolumn '表名 ', '新列名 ', '新列类型 ',加到第几个位置 如: exec addcolumn 'a02 ', 'id2 ', 'char(10) ',2 表示将id2这个列加到表test的第二个位置,类型是char(10)。 select * from a02 参考一下,如果你是查询指定的列的话或许我有办法
go create table aa( id int, clo1 int, col4 int, col2 int, col5 int, col3 int )select * from aa declare @colname varchar(max) set @colname='' select @colname=isnull(@colname,',')+name+',' from syscolumns where id=object_id('aa') order by name --print @colname set @colname='select id,'+left(@colname,len(@colname)-4)+' from aa' exec(@colname) /* 笨办法一个 id col1 col2 col3 col4 col5 */
物料编码 物料名称 单位 2011-06 2011-09 2011-05 2011-10 2011-08 2012-01 2012-02 2011-11 2012-03 2011-12 2012-04 2011-07......用select 语句如何可以让里面的日期字段按顺序显示
create proc addcolumn
@tablename varchar(30), --表名
@colname varchar(30), --要加的列名
@coltype varchar(100), --要加的列类型
@colid int --加到第几列
asdeclare @colid_max int
declare @sql varchar(1000) --动态sql语句
--------------------------------------------------
if not exists(select 1 from sysobjects
where name = @tablename and xtype = 'u ')
begin
raiserror 20001 '没有这个表 '
return -1
end
--------------------------------------------------
if exists(select 1 from syscolumns
where id = object_id(@tablename) and name = @colname)
begin
raiserror 20002 '这个表已经有这个列了! '
return -1
end--保证该表的colid是连续的
select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)if @colid > @colid_max or @colid < 1
set @colid = @colid + 1
--------------------------------------------------
set @sql = 'alter table '+@tablename+ ' add '+@colname+ ' '+@coltype
exec(@sql)select @colid_max = colid
from syscolumns where id = object_id(@tablename) and name = @colname
if @@rowcount <> 1
begin
raiserror 20003 '加一个新列不成功,请检查你的列类型是否正确 '
return -1
end
--------------------------------------------------
--打开修改系统表的开关
EXEC sp_configure 'allow updates ',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1
set @sql = 'update syscolumns
set colid = -1
where id = object_id( ' ' '+@tablename+ ' ' ')
and colid = '+cast(@colid_max as varchar(10))
exec(@sql)--将其他列的列号加1
set @sql = 'update syscolumns
set colid = colid + 1
where id = object_id( ' ' '+@tablename+ ' ' ')
and colid > = '+cast(@colid as varchar(10))
exec(@sql)--将新列列号复位
set @sql = 'update syscolumns
set colid = '+cast(@colid as varchar(10))+ '
where id = object_id( ' ' '+@tablename+ ' ' ')
and name = ' ' '+@colname + ' ' ' '
exec(@sql)
--------------------------------------------------
--关闭修改系统表的开关
EXEC sp_configure 'allow updates ',0 RECONFIGURE WITH OVERRIDE
go调用方法:
exec addcolumn '表名 ', '新列名 ', '新列类型 ',加到第几个位置
如:
exec addcolumn 'a02 ', 'id2 ', 'char(10) ',2
表示将id2这个列加到表test的第二个位置,类型是char(10)。
select * from a02 参考一下,如果你是查询指定的列的话或许我有办法
go
create table aa(
id int,
clo1 int,
col4 int,
col2 int,
col5 int,
col3 int
)select * from aa
declare @colname varchar(max)
set @colname=''
select @colname=isnull(@colname,',')+name+','
from syscolumns where id=object_id('aa') order by name
--print @colname
set @colname='select id,'+left(@colname,len(@colname)-4)+' from aa'
exec(@colname)
/*
笨办法一个
id col1 col2 col3 col4 col5
*/