2

How to know the Column position in a table in MS-SQL.
Eg: if a table consists of 3 columns namely column1, column2 and column3.
I should write a query so that i can get the position of column3 as 3

Vamsi
  • 619
  • 3
  • 9
  • 22
  • If you want to select the column3 before column1 then you can achieve that using SELECT statement. – Himanshu Sep 23 '13 at 07:29
  • 2
    In SQL, as much as possible, you should be ignorant of the "position" of columns - columns are referenced by *name*, not by position. What *problem* are you trying to solve such that finding the position of the column appears to you to be part of the solution? – Damien_The_Unbeliever Sep 23 '13 at 07:32

4 Answers4

1

You will get all these from information_schema. 

select ordinal_position from information_schema.columns 
where schema_name = 'databasename'
and table_name = 'tablename'
and column_name = 'column name'
Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
  • 2
    you have to include schema_name in your query, there're could be two tables with name 'tablename' in different schemas – Roman Pekar Sep 23 '13 at 07:36
1

There're two ways to do this:

select colid
from sys.syscolumns
where id = object_id('schemaname.tablename') and name = 'column3'

and

select ordinal_position
from information_schema.columns 
where
    schema_name = 'schemaname' and
    table_name = 'tablename' and
    column_name = 'column3'

Here's an article about why you have to avoid information_schema views - The case against INFORMATION_SCHEMA views, I don't have to write this types of query often, so I don't really care about it, but sys.syscolumns tends to be a bit faster because it doesn't have many redundant joins which you may not need.

OTOH, information_schema views are ISO standard - here's dicussion about this - SQL Server: should I use information_schema tables over sys tables?

Community
  • 1
  • 1
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
0

try :

SELECT ORDINAL_POSITION
FROM information_schema.columns
WHERE table_name = 'YourTableName' AND COLUMN_NAME = 'YourColumnName'
NeverHopeless
  • 11,077
  • 4
  • 35
  • 56
4b0
  • 21,981
  • 30
  • 95
  • 142
0

Try the query and check for the result.

select column_name,ordinal_position from information_schema.columns where table_catalog = 'yourdatabasename' and table_schema = 'yourschemaname' and table_name = 'yourtablename'

Abhishek Chakraborty
  • 31
  • 4