Drop temporary tables whilst connected ?

[Ryan]

Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.

Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)

However, what I was wondering is :

Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.

Example

SQL Statement with several parts, each uses a series of temporary
tables to create a result set. At the end of a section, these work
tables are no longer needed, so drop table commands are used. The
final result set brings back the combined results from each section
and then drops those at the end.

TIA

Ryan

[Erland Sommarskog]

Re: Drop temporary tables whilst connected ?

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> Just a quicky about temporarary tables. If using QA, when you create a
> temporary table, it gets dropped if you close the query. Otherwise you
> need to state 'DROP TABLE myTable' so that you can re-run the query
> without the table being there.
>
> Sometimes, you can have quite lengthy SQL statements (in a series)
> with various drop table sections throughout the query. Ideally you
> would put these all at the end, but sometimes you will need to drop
> some part way through (for ease of reading and max temp tables etc...)
>
> However, what I was wondering is :
>
> Is there any way to quickly drop the temporary tables for the current
> connection without specifying all of the tables individually ? When
> testing/checking, you have to work your way through and run each drop
> table section individually. This can be time consuming, so being
> naturally lazy, is there a quick way of doing this ? When working
> through the SQL, it's possible to do this quite a lot.[/color]

No, there is no "DROP TABLE #%".

You could write a cursor over tempdb..sysobje cts which finds the tables,
but then you would have to mask out the part which is tacked on to the
table name. Kind of messy.

On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.

If using a stored procedure is problematic for some reason, a RAISERROR
with level 21 is a brutal way if getting rid of the temp tables - in
fact, this kills your connection. Only do this, if you are your own DBA,
because it may ping an alert for an operator on a big server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Drop temporary tables whilst connected ?

> On the other hand, why not pack everything in a stored procedure? A temp[color=blue]
> created in a scope is dropped when that scope exits. Thus, with a stored
> procedure, this is a non-problem.[/color]

Most of this type of query will be put into a stored procedure once
finished, but we most often need to work through it in stages to check
that we have the maths correct at each stage before we progress this
further into an SP. We do a lot of manipulating financials so need to
check our maths throughout. We have a lot of reports based on our
figures and each needs to use the same logic but slightly different
groups of answers which needs checking.

In a lot of cases the SQL can be over a thousand lines long, so we
tend to break it down as much as possible in order to keep it simple.
Hence grouping the drop table statements so we can work with it.

Thanks

Ryan