SQL Server : select columns not by name

Question

Is it possible in SQL Server 2008 to select columns not by their names, but in the order as they appear in the table?

The reason is that i want to select the first 5 oder 6 columns of a table, no matter what's the content, because it is possible that their names or the columns self can be changed or moved.

This looks like the same question (except they want 2 columns and you want 5/6 columns): http://stackoverflow.com/questions/4924365/sql-to-return-first-two-columns-of-a-table — Tricky12, Sep 09 '13 at 13:19
If you think the columns names will be changed or they wil be moved, then you have a serious design problem. Tables should not be changed frequently at all and column order should never under any circumstances be changed in a table. You cahnge the column order in the selct not in the table itself. — HLGEM, Sep 09 '13 at 13:30
@HLGEM: I know that this is a hard problem, but the designing of the struktur and editing of data is not under my influence thus I have no grants to change anything there. I would make a completely new structur, but the bad way is the decision of my boss... — kinske, Sep 09 '13 at 13:48

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

For the first 5 columns you can try this:

select column_name,ordinal_position
  from information_schema.columns
 where table_schema = ...
   and table_name = ...
   and ordinal_position <= 5

Hope this works now. Solution found here. Edit: Updated answer - old one only selected first 5 rows, not columns.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 09 '13 at 13:18

sekky

834
7
14

He needs columns, not rows. – Tricky12 Sep 09 '13 at 13:19

SQL Server : select columns not by name

1 Answers1