求sql语句查询第2列的字段名

Question

求sql语句查询第2列的字段名

Answer 1

select name from (
select row_number()over(order by getdate()) as id,
name from syscolumns where id=object_id('表名'))a where id=2

Answer 2

select * from sys.columns  where object_id=object_id('a')
a为你的表名
上句为具体得出你的列标识符
理论说上，第二列的的名字可以直接
select col_name(object_id('test.dbo.a'),2)  as 列名;

Answer 3

declare @table_name varchar(200)
set @table_name = 'test'--表名称
select o.name as table_name,c.name as colcumn_name,c.colorder,c.* 
from syscolumns c,sysobjects o
where c.id = o.id
and o.xtype = 'u'
and o.name = @table_name
and c.colorder = 2--只取第2个字段

Answer 4

select name,colorder from syscolumns
where id = object_id('authors')
order by colorder
/*
name                                                                                                                             colorder 
-------------------------------------------------------------------------------------------------------------------------------- -------- 
au_id                                                                                                                            1
au_lname                                                                                                                         2
au_fname                                                                                                                         3
phone                                                                                                                            4
address                                                                                                                          5
city                                                                                                                             6
state                                                                                                                            7
zip                                                                                                                              8
contract                                                                                                                         9（所影响的行数为 9 行）
*/select name,colid from syscolumns
where id = object_id('authors') and colorder = 2/*
name                                                                                                                             colid  
-------------------------------------------------------------------------------------------------------------------------------- ------ 
au_lname                                                                                                                         2（所影响的行数为 1 行）
*/