How to drop temp tables from tempdb

[johndimitri]

Is there any to drop the temporary tables from tempdb ?

[Sherlock, Kevin]

drop table #mytable

drop table tempdb..mytable

drop table tempdb.guest.mytable

drop table tempdb.dbo.mytable

<John Dimitri> wrote in message news:472a2c66.10e...@sybase.com...

[johndimitri]

I am talking about the temporary session tables in tempdb

[Jason L. Froebe [TeamSybase]]

The #mytable is the temporary session table for the current session. If
you mean for other connections, you will have to restart ASE or kill the
other connections.

--
Jason L. Froebe
http://www.froebe.net/blog
http://k2p2.net
Team Sybase

[Sherlock, Kevin]

drop table #temporary_session

:)

what do you mean by "temporary session tables"?

Can you give me an example of an isql script where you create one that you want
to drop?

<John Dimitri> wrote in message news:472a2f8a.114...@sybase.com...

[Mark K]

John,

Jason is correct that you can only drop #temp tables for your session.
However, there is dbcc ophantables('drop') that can be used to clean up
after a spid that did not properly clean up at termination.

More details about the problem you are trying to solve might be beneficial.

Mark Kusma

<John Dimitri> wrote in message news:472a2f8a.114...@sybase.com...

[Jason L. Froebe [TeamSybase]]

Mark K wrote:
> John,
>
> Jason is correct that you can only drop #temp tables for your session.
> However, there is dbcc ophantables('drop') that can be used to clean up
> after a spid that did not properly clean up at termination.
>
> More details about the problem you are trying to solve might be beneficial.
>
> Mark Kusma
>

Hi Mark,

Is dbcc orphantables a fully supported dbcc or should it be considered
undocumented / unsupported?

thanks

[Mark K]

Hi Jason,

Before posting, I checked the online doc and couldn't find it, even though
it has been around a few years. I guess that would make it "undocumented".
It is in Rob's book as an undocumented command. That said, I'm guessing you
would get help if you called about it. (Note: I'm not committing to anything
here, but it is used enough that it shouldn't be a problem getting support).

I do see it was previously mentioned in the newsgroups and I see this:
http://search.sybase.com/kbx/changerequests?bug_id=317976

Mark Kusma

"Jason L. Froebe [TeamSybase]" <ja...@froebe.net> wrote in message
news:472b87c6$1@forums-1-dub...

[Jason L. Froebe [TeamSybase]]

Mark K wrote:
> Hi Jason,
>
> Before posting, I checked the online doc and couldn't find it, even though
> it has been around a few years. I guess that would make it "undocumented".
> It is in Rob's book as an undocumented command. That said, I'm guessing you
> would get help if you called about it. (Note: I'm not committing to anything
> here, but it is used enough that it shouldn't be a problem getting support).
>
> I do see it was previously mentioned in the newsgroups and I see this:
> http://search.sybase.com/kbx/changerequests?bug_id=317976
>
> Mark Kusma

Thanks Mark!

[Rob Verschoor]

Yup -- it's been mentioned at past conferences, but I was playing it safe
;-)
It's really just as undocumented as many of the other dbcc commands...

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

[Heinz Müller]

Maybe this helps.
I have had a similiar question and got a hint from Mark Parson:

<snip>

I'm assuming you also can't run stuff like sp_help, sp_helpindex or
sp_spaceused on 'tmp_CFRepoDeals26805', right?
If correct, run the following while you're in the tempdb database:

-- A
select user_name()
go

-- B
select user_name(uid),name from sysobjects
where type = 'U' and name = 'tmp_CFRepoDeals26805'
go

Does the user name from 'A' match the user name from 'B'?

If not, you could try a few options:

1 - setuser '<user_name_fromB'> ... then issue drop table
'tmp_CFRepoDeals26805'
2 - drop table '<user_name_fromB>.tmp_CFRepoDeals26805'
3 - get someone with sa_role (or sso_role?) to perform #1

Depending on the various user_name() results from A & B, you may (not) find
that some of the above work ... *dang*
ownership and permissions schtuff ...

</snip>

Please take a look in sybase.public.ase.administration at 18.08.2007

Regards
Heinz