How to identify columns order in a table

Question

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?

Answer 1

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.

Answer 2

I would like to offer also INFORMATION_SCHEMA views. These are ANSI Standard and works cross database ,for databases which support them.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

Answer 3

If you want/need the columnIDs in sequence, I have used:

select 
    ROW_NUMBER() OVER (PARTITION BY OBJECT_NAME(object_id) ORDER BY Column_ID) as ColumnIDSeq,
    *
from sys.columns
where OBJECT_NAME(object_id) = 'MyTable'
order by column_id