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.
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.
What is the purpose of getting that number ?
Seems like you have some misconception about this too. The number does not identified the row in the table. It numbered the rows in sequence and is just for presentation only. The ordering of rows is depends on theIn Table, we can see there is number assigned automatically (line number)
ORDER BY clause in the query
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
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
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
This query is working but i need to define table name and column name. My requirement is the query should automatically detects the table and column name.
Hi @SarveshPandey-7139 ,
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
Hi @EchoLiu-msft ,
My requirements, I have source database of more then 100 tables and each table is going under transactions. I and able to get and move those set of transactions to staging table but I need to create a SQL Dynamic query for Insert, Update and Delete to reflect those change from staging to destination table.
We tried different methods to implement this but column count create a difficulty as its varies.
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.
i am planning to call the column name on the basis of its column number as i have multiple tables and each table have different column name and count.
So, by getting the column number i can call column which will be helpful to reflect the Insert, Update and Delete records from staging to destination table.
Single query for 100's pf tables.
You should use column name for that purpose. The ordinal position of columns is not important and cannot be used for anything in SQL Server.
This query is working but i need to define table name and column name. My requirement is the query should automatically detects the table and column name.
Your requirement is still unclear, but I like to point out that an SQL query always refers to a fixed of tables and columns. An SQL query as such is never dynamic.
But you can build queries dynamically by means of string manipulation, and possibly by reading metadata tables. String manipulation can be done in any language, including T-SQL, but there are language that are equipped for the task.
12 people are following this question.