CREATE TABLE bm(bmid int,bmname varchar(10) ,username varchar(10) ,hpmc varchar(10) ,gz int) insert into bm select 1,'销售一部','小张','货品A',1000 insert into bm select 2,'销售二部','小王','货品B',3000 insert into bm select 3,'销售三部','小乐','货品C',2000 insert into bm select 4,'销售四部','小马','货品D',1000declare @sql varchar(8000) select @sql=isnull(@sql+',','')+name from syscolumns where id=object_id('bm') and name <>'gz' exec(' select '+@sql+' from bm') bmid bmname username hpmc 1 销售一部 小张 货品A 2 销售二部 小王 货品B 3 销售三部 小乐 货品C 4 销售四部 小马 货品D
得到表中除Col1、Col2的所有列例如:userno_fm、userno_to create table test( num int identity(1,1), userno_fm varchar(10), userno_to varchar(10), username varchar(10)) select * from testdeclare @sql varchar(8000) select @sql='' select @sql=@sql+','+[name] from (select [name] from syscolumns where object_id(N'[test]')=[id] and [name] not in ('userno_fm','userno_to')) A set @sql='select '+stuff(@sql,1,1,'')+' from [test]' --print @sql exec (@sql)drop table test
在select 字段列表时 没有 except 这样类似的关健字.懒得打的话,可以打开查询分析器,对象浏览器,选定库,选定表,展列"列",然后在列上拖去.再删除你不要的.
insert into bm select 1,'销售一部','小张','货品A',1000
insert into bm select 2,'销售二部','小王','货品B',3000
insert into bm select 3,'销售三部','小乐','货品C',2000
insert into bm select 4,'销售四部','小马','货品D',1000declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+name
from syscolumns where id=object_id('bm')
and name <>'gz'
exec(' select '+@sql+' from bm')
bmid bmname username hpmc
1 销售一部 小张 货品A
2 销售二部 小王 货品B
3 销售三部 小乐 货品C
4 销售四部 小马 货品D
create table test(
num int identity(1,1),
userno_fm varchar(10),
userno_to varchar(10),
username varchar(10))
select * from testdeclare @sql varchar(8000)
select @sql=''
select @sql=@sql+','+[name] from
(select [name] from syscolumns where object_id(N'[test]')=[id] and [name] not in ('userno_fm','userno_to')) A set @sql='select '+stuff(@sql,1,1,'')+' from [test]'
--print @sql
exec (@sql)drop table test