• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

Changing a tables id?

I have a Sybase SQL Server 10, running on a Sparc Solaris 2.6.
I have a couple of tables that share a user datatype, TableId which is a smallint.
We have reached 32600 odd, and will soon run out of id's.

I have defncopied and bcp'd the data out,
recreated the table with a different datatype, ItemId
which is an int. Now the interesting bit........

When I update the id of my new table to what my old table was,
Sybase can no longer see it, and the shell for my isql session hangs if I try to access the table.

How do I recreate this table with a bigger datatype and retain its old id?
Are there any other tables I have to update aswell?

1 Solution
In order to accomodate the larger data type(int instead of smallint), you have to bcp out, recreate the table and bcp in data. Once you create the new table ,server allocates unique-id to the object (table) and these ids are not in serial or sequential order. When you change the 'id' in sysobjects for the table. The internal object_id is not changed. Sybase Server always refers the table by the internal object-id. Even you update the id , the internal-id is not changed. The object information also stores in the system tables such as sysindexes,syscolumns,sysdepends,syskeys,sysconstraints and sysreferences of the user database. However the entries in all these system table refers to the internal object_id of the sysobjects. After changing the id of the table, you can see the id and obect_id("table_name") are different. So updating "id" does not change any references unless your procedure or query  has hardcoded value for the old id of the table. In my opinion you need not to update any other system tables. Accessing the table will never be a problem unless you are trying to match "id" instead of object_id().

By the way ,data type for the 'id' in sysobjects table is "int" and this is literally a large value. I don't think the server will  run out of id.
However changing the id in system tables is not recommonded by Sybase.

Hope this will help you.
Thanks ...

rickyrAuthor Commented:
While accessing these new table won't be a problem when referencing the table by name, like you said I have loads of sp's that have the id hardwired. I'd like to know a way of slyly changing the table id so these sp's won't know that I have changed the table.
sorry to reject, but I'm still stuck.
thanks for your time
Well, let me first say that Sybase certainly doesn't support this...

....but I do think it can be done.  There are many places in the system catalogswhere you would have to update the id..

Take a dump of the database first, in case you hose a system table doing any of this.

See docid 20230 as a good (possibly complete) starting point.  Read it so you understand what it is doing and why.  Then extrapolate to what you want to do: whereever that script says "delete <systemtable> where id = <id>", substitute "update".  Don't go on to the extentzap step.  When done, reboot the server before trying to access the table (this will refresh the in-memory structures).

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now