CODEWITHSUNDEEP
sqlsql-servert-sql
Vamsi
Vamsi

Reputation: 679

Position of a column in a table

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

Upvotes: 2

Views: 1237

Answers (4)

4b0
4b0

Reputation: 22323

try :

SELECT ORDINAL_POSITION
FROM information_schema.columns
WHERE table_name = 'YourTableName' AND COLUMN_NAME = 'YourColumnName'

Upvotes: 0

roman
roman

Reputation: 117380

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?

Upvotes: 1

Abhishek Chakraborty
Abhishek Chakraborty

Reputation: 31

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'

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

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'

Upvotes: 1

Related Questions