Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com Mar 12 '12 at 3:32

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

add comment

3 Answers 3

up vote 7 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
add comment

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
share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

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.