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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.