Hcfyk

I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?

select * from sys.columns c

where c.object_id = object_id('Customer')

order by column_id





create table dbo.Customer

(

    CustomerId int primary key,

    CustomerName varchar(255),

    CustomerAddress varchar(255),

    EnrollmentDate date

)

Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:

Column name  Data type  Description

-----------  ---------  ---------------------------------------------- 

column_id:   int        ID of the column. Is unique within the object.

                        Column IDs might not be sequential.

column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.

As the documentation states, column_id values may not be sequential if you drop a column from a table.

You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.

Consider a quick example:

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL

DROP TABLE dbo.t;

CREATE TABLE dbo.t

(

    c1 int

    , c2 int

    , c3 int

    , c4 int

);



ALTER TABLE dbo.t DROP COLUMN c1;

ALTER TABLE dbo.t ADD c5 int;

ALTER TABLE dbo.t ALTER COLUMN c2 char(3);



SELECT o.name

    , c.name

    , c.column_id

    , ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')

FROM sys.columns c

    INNER JOIN sys.objects o ON c.object_id = o.object_id

WHERE o.name = N't'

The output looks like:

╔══════╦══════╦═══════════╦═════════╗

║ name ║ name ║ column_id ║ ordinal ║

╠══════╬══════╬═══════════╬═════════╣

║ t    ║ c2   ║         2 ║       1 ║

║ t    ║ c3   ║         3 ║       2 ║

║ t    ║ c4   ║         4 ║       3 ║

║ t    ║ c5   ║         5 ║       4 ║

╚══════╩══════╩═══════════╩═════════╝

Just to propose an additional answer that will tell you the actual column position instead of column_id

select column_name, ORDINAL_POSITION 

from INFORMATION_SCHEMA.COLUMNS

where table_name = 'your_table'