How to identify columns order in a table

I need to list columns from a table in the table definition order:

select * from syscolumns
where id = object_id('MyTable')
--order by colid

By examining syscolumns tables two columns look relevant: colid and colorder. The MSDN article on syscolumns says:

colid    | smallint | Column or parameter ID.
colorder | smallint | Identified for informational purposes only. 
                    | Not supported. Future compatibility is not guaranteed.

I tried to run

select * from syscolumns where colorder <> colid

which yielded no rows, and that makes me think that these columns has the same values most of the time.

It does look that the safest bet is to use colid. However I would be curious to know: is there a difference between these two columns, and if there is, what is this difference?

Also the MSDN article, does not confirm, that colid reflects the order of the table definition. While this is reasonable to assume that this is the case, could you please let me know, if you are sure that it's the case, how you know that this is?

You should be using the sys.columns catalog view. syscolumns is included only for backwards compatibility. It's really a SQL Server 2000 system table that shouldn't be used in SQL Server 2008 R2.

select *
from sys.columns
where object_id = object_id('MyTable')
order by column_id

That should return the order of your columns. Note, though, these column id's might not be sequential.

StackExchange Database Administrators Q#14791, answer score: 15