declare @s as varchar(8000) set @s='' select top 20 @s=@s+[name]+',' from syscolumns where id in(select id from sysobjects where name='表名') print @s set @s=left(@s,len(@s)-1) exec('select '+@s+' from 表名')
--需要用动态SQL语句 declare @sql varchar(8000) set @sql='select ' select top 5 @sql=@sql+a.name+',' from syscolumns a where a.id=object_id('表') order by colid set @sql=left(@sql,datalength(@sql)-1)+' from 表' exec(@sql)
--需要用动态SQL语句 declare @sql varchar(8000) set @sql='select ' select top 20 @sql=@sql+a.name+',' from syscolumns a where a.id=object_id('表') order by colid set @sql=left(@sql,len(@sql)-1)+' from 表' exec(@sql)
select identity(int,1,1) as xh,* into #a from TB
select top 20 * from #a order by xh desc
select top 20 * from tb
order by COL desc
这样不可以嘛?
另外,要取列的信息
恐怕得访问系统表了(除非列名已知且有规律)提供另外一种办法:
如果列名确定的话,把列名按顺序存入一张自定义的表内
然后以要取的列数为参数写一个存储过程
从表中读出需要的列的信息拼成一条SQL语句并执行即可
set @s=''
select top 20 @s=@s+[name]+',' from syscolumns where id in(select id from sysobjects where name='表名')
print @s
set @s=left(@s,len(@s)-1)
exec('select '+@s+' from 表名')
declare @sql varchar(8000)
set @sql='select '
select top 5 @sql=@sql+a.name+',' from syscolumns a where a.id=object_id('表') order by colid
set @sql=left(@sql,datalength(@sql)-1)+' from 表'
exec(@sql)
declare @sql varchar(8000)
set @sql='select '
select top 20 @sql=@sql+a.name+',' from syscolumns a where a.id=object_id('表') order by colid
set @sql=left(@sql,len(@sql)-1)+' from 表'
exec(@sql)