I posted before about getting the columns in a table by using the sys.columns table. In there I ordered the columns by name, but today I had a need to do the exact same thing except I wanted the columns in the same order in which they appear in the table. Here's what I did (it's almost exactly the same, which is why this is post 2(A) instead of 3).
DECLARE @TableName VARCHAR(100),
@ColumnName VARCHAR(1000),
@ColumnID INT,
@SelectStatement VARCHAR(8000)
SET @TableName = 'SampleTable'
SET @SelectStatement = 'SELECT '
DECLARE datacursor CURSOR FOR
SELECT DISTINCT sc.[name],
sc.column_id
FROM sys.columns sc WITH (nolock)
INNER JOIN sys.tables st WITH (nolock)
ON sc.object_id = st.object_id
WHERE st.[name] = @TableName
ORDER BY sc.column_id
OPEN datacursor
FETCH next FROM datacursor INTO @ColumnName, @ColumnID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SelectStatement = @SelectStatement + @ColumnName + ', '
FETCH next FROM datacursor INTO @ColumnName, @ColumnID
END
CLOSE datacursor
DEALLOCATE datacursor
SET @SelectStatement = Substring(@SelectStatement, 1, Len(@SelectStatement) - 1)
SET @SelectStatement = @SelectStatement + ' FROM ' + @tableName
SELECT @SelectStatement
