Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Mar 12 '12 at 3:32

This question came from our site for professional and enthusiast programmers.

up vote 9 down vote accepted

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.

share|improve this answer

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

share|improve this answer

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

from sys.columns
where OBJECT_NAME(object_id) = 'MyTable'
order by column_id
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.