Does SQL Table Column is assigned with column number

Sarvesh Pandey 141 Reputation points
2020-12-03T12:49:37.227+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} vote

6 answers

Sort by: Most helpful
  1. Olaf Helper 40,576 Reputation points
    2020-12-03T13:13:29.95+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

  2. Scott White 1 Reputation point
    2020-12-03T13:25:27.437+00:00

    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

    0 comments No comments

  3. Viorel 111.5K Reputation points
    2020-12-03T22:06:21.59+00:00

    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
    

  4. EchoLiu-MSFT 14,571 Reputation points
    2020-12-04T05:47:55.907+00:00

    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


  5. Tom Phillips 17,716 Reputation points
    2020-12-04T15:59:46.55+00:00

    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.