Cannot access a table named "temp"

[SantaMufasa]

Can someone spot what I'm doing wrong?

1> select name from sysobjects where type = 'U' and name = 'temp'
2> go
 name
 ------------------------------
 temp

(1 row affected)
1> select * from temp
2> go
Msg 156, Level 15, State 1:
Line 1:
Incorrect syntax near the keyword 'temp'.
1> drop table temp
2> go
Msg 156, Level 15, State 1:
Line 1:
Incorrect syntax near the keyword 'temp'.
1>

If there is (obviously) a table named "temp", then what must I do to query it, or, preferrably, "DROP table temp"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Sybase Gentlepeople,

Should I be concerned that no one has responded to any thread in this forum since yesterday?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dbalearner]

Santa, temp is a reserved word in Sybase. I think this a legacy table from an old upgrade. So we need to get rid of it in catalog.

please do (as sa)

sp_configure 'allow updates',1 -- allow changes to cat
go
update sysobjects set name = 'temp1' where name = 'temp'and type = 'U'
go
drop table temp1
go

-- see how it goes

 

[dbalearner]

An easier way. Just try this Santa

1> set quoted_identifier on
2> go
1> create table "temp" ("a" int not null)
2> go
1> drop table temp
2> go
Msg 156, Level 15, State 2:
Server 'lon_gen_sql_tst1', Line 1:
Incorrect syntax near the keyword 'temp'.
1> drop table "temp"
2> go
1> set quoted_identifier off
2> go

 

[dbalearner]

And of course you can rename and drop it

EXEC sp_rename "temp", "old_temp"
go
drop table old_temp
go

 

[SantaMufasa]

DBALearner,

Thanks for your postings. Following are the instructions that I received from Sybase Technical Support to get rid of all evidence of a table named "temp":

Here is the procedure for manually deleting a user-defined table from the system tables.

The first thing you need to do is to find out the object_id of the table. You should be able to issue the following to get this:

1>use <dbname>
2>go
1>select id from sysobjects where name = 'test'
2>go

If you get an error, then try issuing:

1>select id, name from sysobjects where type = 'U'
2>go

Look through and find the one that has 'test' as the name.

After you have the object id, you can then perform the following procedure.

1>use master
2>go
1> sp_configure "allow updates", 1
2>go

2. use the database; get its dbid [select db_id()] and write it
down for reference. I gave the instructions for this above.

3. select id from sysobjects where name = <bad-table-name>
... write that down, too. I gave the instructions for this above.

4. select indid from sysindexes where id = <table-id>
... you will need these index IDs to run dbcc extentzap. Also,
remember that if the table has a clustered index you will need
to run extentzap on index "0", even though there is no sysindexes
entry for that indid.

5. begin transaction
... not required, but a *really*good*idea*.

6. Type in this short script:

declare @obj int
select @obj = id from sysobjects where name = <bad-table-name>
delete syscolumns where id = @obj
delete sysindexes where id = @obj
delete sysobjects where id = @obj
delete sysprocedures where id in
(select id from sysdepends where depid = @obj)
delete sysdepends where depid = @obj
delete syskeys where id = @obj
delete syskeys where depid = @obj
delete sysprotects where id = @obj
delete sysconstraints where tableid = @obj
delete sysreferences where tableid = @obj

...This gets rid of all system catalog information for the object,
including any object and procedure dependencies that may be present.
Some of these lines may be unnecessary; you should type them in
anyway just for the exercise.

7. commit transaction
(unless you made a mistake in step 6, in which case rollback.)

8. Prepare to run dbcc extentzap:

1>use master
2>go
1>sp_dboption <db-name>, read, true
2>go
1>use <db-name>
2>go
1>checkpoint
2>go

9. Run dbcc extentzap once for EACH index (including index 0, the data
level) that you got from step 4 above:


**********

The following commands are very dangerous commands
use them with care because, if you give the wrong object id,
all data for that object will be lost forever. You want to
make sure that the object id is the id of the bad table and
not one of your good objects. Also be sure that the server
has been properly backed up. Generally speaking it would be
a good idea to do the complete opertation in test before
executing it in production.

**********

dbcc traceon(3604) /* lets you see errors */
dbcc extentzap( <db-id>, <object-id>, <index-id>, 0)
dbcc extentzap( <db-id>, <object-id>, <index-id>, 1)

(Type "go" after each command)

Notice that extentzap runs TWICE for each index ... this is because
the last parameter (the "sort" bit) might be 0 or 1 for each index,
and you want to be absolutely sure you clean them all out.

10. Clean up after yourself:

1>use master
2>go
1>sp_configure allow,0
2>go
1>use <db-name>
2>go
1>checkpoint
2>go

You will then need to recycle ASE after manually deleting the table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dbalearner]

Thanks Santa. It is like a breaking a nut with sledgehammer! Since in Sybase the ID in sysobjects matters NOT the name. So I would say doing the following does the job

1> set quoted_identifier on
2> go
3> drop table "temp"
2> go
1> set quoted_identifier off
2> go

Regards,

Learner