COLID discrepancy in Syscolumns / Information_Schema.Columns - SQL Server 2000

[Tina Harris]

I ran the following query in Query Analyzer for a 7 column table.

SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY
c.colid

The results were:
I_CSD 1
X_STE_XML 2
I_USR_LCK 4
T_CRT_RCD 5
I_USR_CRT_RCD 6
T_UDT_RCD 7
I_USR_UDT_RCD 8

If I use the information_sch ema view (SELECT column_name,
ordinal_positio n FROM information_sch ema.columns WHERE table_name =
'CSD_XML') I get the same results.

The problem is that the colids go from 2 to 4 and the colids go
through 8 when there are only 7 columns.

At one time there was another column in the table, but it has since
been dropped and isn't there anymore. It seems that the colids in
syscolumns did not update when the column was dropped.

Is this because of the way I dropped the column? Is there anything I
can do now that it has happened?

[Erland Sommarskog]

Re: COLID discrepancy in Syscolumns / Information_Sch ema.Columns - SQL Server 2000

Tina Harris (imtigertoo@yah oo.com) writes:[color=blue]
> I ran the following query in Query Analyzer for a 7 column table.
>
> SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY
> c.colid
>
> The results were:
> I_CSD 1
> X_STE_XML 2
> I_USR_LCK 4
> T_CRT_RCD 5
> I_USR_CRT_RCD 6
> T_UDT_RCD 7
> I_USR_UDT_RCD 8
>
> If I use the information_sch ema view (SELECT column_name,
> ordinal_positio n FROM information_sch ema.columns WHERE table_name =
> 'CSD_XML') I get the same results.[/color]

I suspect that you have a real problem where this becomes an obstacle.
Maybe you could explain?
[color=blue]
> Is this because of the way I dropped the column? Is there anything I
> can do now that it has happened?[/color]

Yes, I would suppose this is because you dropped a column. To
collapse colid into a contiguous sequence, you would have to 1) rename
the table 2) recreate it 3) move data over 4) move referencing foreign
keys 5) drop the old table 6) restore triggers and indexes.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Eric]

Re: COLID discrepancy in Syscolumns / Information_Sch ema.Columns - SQL Server 2000

I'm having exactly the same issue on some tables of mine (Running SQL
Server 2000 SP3)

Is it true that the colid should be contiguous in syscolumns ? Or does
it accept "holes" in the numbering ?

I suspect these holes to mess up my merge replication (some rows do
not replicate anymore, and it happens to be rows from these tables
with holes).

Another thing I noticed about these tables with holes : running
sp_showrowrepli cainfo on them runs a infinite loop, because this
stored procedure apparently takes it for granted that colid are
consecutive (contiguous).

Hope this helps, any advice welcome.
--Eric

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns941D29 B95DD9Yazorman@ 127.0.0.1>...[color=blue]
> Tina Harris (imtigertoo@yah oo.com) writes:[color=green]
> > I ran the following query in Query Analyzer for a 7 column table.
> >
> > SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY
> > c.colid
> >
> > The results were:
> > I_CSD 1
> > X_STE_XML 2
> > I_USR_LCK 4
> > T_CRT_RCD 5
> > I_USR_CRT_RCD 6
> > T_UDT_RCD 7
> > I_USR_UDT_RCD 8
> >
> > If I use the information_sch ema view (SELECT column_name,
> > ordinal_positio n FROM information_sch ema.columns WHERE table_name =
> > 'CSD_XML') I get the same results.[/color]
>
> I suspect that you have a real problem where this becomes an obstacle.
> Maybe you could explain?
>[color=green]
> > Is this because of the way I dropped the column? Is there anything I
> > can do now that it has happened?[/color]
>
> Yes, I would suppose this is because you dropped a column. To
> collapse colid into a contiguous sequence, you would have to 1) rename
> the table 2) recreate it 3) move data over 4) move referencing foreign
> keys 5) drop the old table 6) restore triggers and indexes.[/color]

[Tina Harris]

Re: COLID discrepancy in Syscolumns / Information_Sch ema.Columns - SQL Server 2000

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns941D29 B95DD9Yazorman@ 127.0.0.1>...[color=blue]
> Tina Harris (imtigertoo@yah oo.com) writes:[color=green]
> > I ran the following query in Query Analyzer for a 7 column table.
> >
> > SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY
> > c.colid
> >
> > The results were:
> > I_CSD 1
> > X_STE_XML 2
> > I_USR_LCK 4
> > T_CRT_RCD 5
> > I_USR_CRT_RCD 6
> > T_UDT_RCD 7
> > I_USR_UDT_RCD 8
> >
> > If I use the information_sch ema view (SELECT column_name,
> > ordinal_positio n FROM information_sch ema.columns WHERE table_name =
> > 'CSD_XML') I get the same results.[/color]
>
> I suspect that you have a real problem where this becomes an obstacle.
> Maybe you could explain?[/color]

My problems are 1) I read the tables to build a data dictionary and 2)
I use CA's ERwin to maintain my data models. When I compare my model
to the database, each of the tables that has had a column dropped and
didn't renumber properly shows up as having a difference between the
model and the database. I've got about 200 tables in one of my
databases, so it really gets to be a pain.[color=blue]
>[color=green]
> > Is this because of the way I dropped the column? Is there anything I
> > can do now that it has happened?[/color]
>
> Yes, I would suppose this is because you dropped a column. To
> collapse colid into a contiguous sequence, you would have to 1) rename
> the table 2) recreate it 3) move data over 4) move referencing foreign
> keys 5) drop the old table 6) restore triggers and indexes.[/color]

I really was hoping for some other kind of fix as this particular
database is loaded with constraints and has quite a bit of data, not
to mention that it is an enterprise-wide, high visibility database.

Thanks for your comments.