Incorrect column order in syscolumns / Information_schema.columns

When dropping a column, syscolumns keeps the old ordinal
for the following columns. Is this correct / expected
behavior?
This also affects Information_schema.Columns. See example
below.
Is there any SUPPORTED way to refresh or update this info?
Regards
Hans Schyldt
Drop Table HS_Test
go
CREATE TABLE [dbo].[HS_Test] (
[C1] [int] IDENTITY (1, 1) NOT NULL ,
[C2] [int] NOT NULL ,
[C3] [varchar] (10) NOT NULL ,
[C4] [varchar] (10) NOT NULL) ON [PRIMARY]
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
alter table HS_Test
Drop Column C2
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
Drop Table HS_Test
goThis is correct behavior, but can be annoying.
Lot of people will argue that order and ordinal numbers do not matter, but
they matter to me.
What I did to correct this, was making a change with the Enterprise Manager
and reversing that change. Using such a change that the enterprise manager
rebuilds the whole table. In this way my ordinals where again as they were
in other databases. (So I could compare the databases again). This worked
fast enough so I didn't look for 'simpler' commands which only changed
metadata.
ben brugman
"Hans Schyldt" <hans.reply_to_forum.schyldt@.intentia.se> wrote in message
news:0e4701c367e7$70a16ec0$a101280a@.phx.gbl...
> When dropping a column, syscolumns keeps the old ordinal
> for the following columns. Is this correct / expected
> behavior?
> This also affects Information_schema.Columns. See example
> below.
> Is there any SUPPORTED way to refresh or update this info?
> Regards
> Hans Schyldt
>
> Drop Table HS_Test
> go
> CREATE TABLE [dbo].[HS_Test] (
> [C1] [int] IDENTITY (1, 1) NOT NULL ,
> [C2] [int] NOT NULL ,
> [C3] [varchar] (10) NOT NULL ,
> [C4] [varchar] (10) NOT NULL) ON [PRIMARY]
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> alter table HS_Test
> Drop Column C2
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> Drop Table HS_Test
> go