Problem getting column name

[PeDa]

My application dynamically adds and drops columns from a table. I want to get the current name of the Nth column, and found the following snippet:

Declare @TableName as nVarchar(100);
Declare @NthCol as Int
Select  @TableName =N'MyTableName',
        @NthCol=3
Select Col_name(object_id(@TableName),@NthCol) ColumnName

However this doesn't work if columns are dropped. If I drop, say, the 4th column, and try and get the name of the new forth column (was the fifth column), the code returns NULL; @NthCol = 5 returns the name of the old 5th (now 4th) column.

Any suggestions welcome.

 

[gmmastros]

try this:

Declare @TableName as nVarchar(100);
Declare @NthCol as Int
Select  @TableName =N'Blah',
        @NthCol=3

; With ColumnNames As
(
  Select  Name,
          Row_Number() Over (Order By column_id) As RowId
  From    Sys.Columns
  Where   object_id = object_id(@TableName)
)
Select  Name
From    ColumnNames
Where   RowId = @NthCol

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeDa]

Thank you (once again!); this does indeed give the correct column name.
Peter D.