Link to home
Start Free TrialLog in
Avatar of rickyr
rickyr

asked on

Changing a tables id?

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

Question......
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?

regards
Avatar of dk99
dk99

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

Avatar of rickyr

ASKER

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
regards
ASKER CERTIFIED SOLUTION
Avatar of bret
bret
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial