Does SQL Table Column is assigned with column number

Question

In Table, we can see there is number assigned automatically (line number) is the same thing is available for the columns (column number) and how can we get that number?
I need a SQL query to find the column number not the number of columns.

Answer 1

I need a SQL query to find the column number not the number of columns.

Do you mean the column ordinate / id like this here?

select tab.name, col.name, col.column_id
from sys.columns as col
     inner join
     sys.tables as tab
         on tab.object_id = col.object_id
order by tab.name, col.column_id

Answer 2

I don't think that is possible in sql server. It sounds like you want the column index or column ordinal, you can get that via application code via datareader.getordinal

Answer 3

Try the next approach too:

declare @table_name varchar(max) = 'MyTable'
declare @column_name varchar(max) = 'MyColumn'

declare @column_ordinal int = -1

select @column_ordinal = column_ordinal
from sys.dm_exec_describe_first_result_set('select * from ' + quotename(@table_name), NULL, 0)
where [name] = @column_name

-- show the results
select @table_name, @column_name, @column_ordinal

Answer 4

Hi @Sarvesh Pandey ,

In sql server, the row number needs to be created by yourself, and the leftmost serial number of the data you mentioned is not the row number. Also, your problem description seems unclear. Do you want to add a column number to each column? This seems not easy to achieve. Could you share with us the specific problems you are facing?
And you may not need the column number, because the data storage or search in sql server is based on the row number, not the column number.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 5

What do you intend to use this information for?

As Olaf said, there is an "ordinal position" value for columns in the table. However, this is not useful for anything. You cannot reference columns by column number in SQL Server.