473,242 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

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

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_schema view (SELECT column_name,
ordinal_position FROM information_schema.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?
Jul 20 '05 #1
3 8907
Tina Harris (im********@yahoo.com) writes:
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_schema view (SELECT column_name,
ordinal_position FROM information_schema.columns WHERE table_name =
'CSD_XML') I get the same results.
I suspect that you have a real problem where this becomes an obstacle.
Maybe you could explain?
Is this because of the way I dropped the column? Is there anything I
can do now that it has happened?


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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_showrowreplicainfo 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 <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
Tina Harris (im********@yahoo.com) writes:
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_schema view (SELECT column_name,
ordinal_position FROM information_schema.columns WHERE table_name =
'CSD_XML') I get the same results.


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


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.

Jul 20 '05 #3
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
Tina Harris (im********@yahoo.com) writes:
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_schema view (SELECT column_name,
ordinal_position FROM information_schema.columns WHERE table_name =
'CSD_XML') I get the same results.
I suspect that you have a real problem where this becomes an obstacle.
Maybe you could explain?


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.
Is this because of the way I dropped the column? Is there anything I
can do now that it has happened?


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.


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.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Pachydermitis | last post by:
Hi I need to see all the indexes in a database. The ID has dbo rights to the database, but not to the master. I can't see anything in INFORMATION_SCHEMA.CHECK_CONSTRAINTS or...
3
by: Dave Sisk | last post by:
Hi Folks: I'm a little new to SQLServer, so please pardon my ignorance! I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and TABLE_CONSTRAINTS. I'm looking for the views that will...
3
by: Shawn | last post by:
Hi guys, I'm trying to compose a query that will select all columns from a table, but without any duplicates. E.g. table name is 'tblShipment' columns are: fldUnique(pk) | fldShipNo |...
0
by: Mark Johnson | last post by:
Is it possible that "information_schema.schemata" does not allways work correctly in MSDE Sql-Server. I have built an Explorer that will find the Servers (MSDE and SQL-Server 2000) and then...
14
by: mike.griffin | last post by:
This is part of the Columns View, if you add a numeric field to your table and don't provide any Length or Precision then : numeric_precision is returned as 65535 numeric_scale is returned as...
0
by: BB | last post by:
Hi There, just moved over from SQL Server to mySQL and finding my way about. In SQL Server there was a way of retrieving (and setting) metaproperties for columns and tables. The closest I see to...
1
by: Shrek | last post by:
if I select character_maximum_length from INFORMATION_SCHEMA.COLUMNS if returns -1 on columns that are defined as varchar(max) is this normal???
2
by: amit2781 | last post by:
Hi, I have created 4 tables in 'amit' database and then I deleted them. Still I able to get information about the table_schema for the table deleted. After drop table when I fire a query for...
2
by: smileprince00 | last post by:
what are metadata fnctions....how they can help the programming thank you regards rohit
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.