November 18, 2005 at 9:58 am
A co-worker has a problem regarding temporary tables. He wants to create a temporary table each time his job runs, so he uses this code snippet to make sure it is deleted before recreating it.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#searchResult%')
DROP TABLE #searchResult
GO
CREATE TABLE #searchResult(result int)
He claims when his job fails, the temp table is still in the system but the above "if exists" statement can't find it.
Anybody experience anything like that?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 18, 2005 at 10:39 am
Try
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#searchResult%')
I perfer the following
IF OBJECT_ID('tempdb.dbo.#searchResult') IS NOT NULL
Tim S
November 18, 2005 at 12:22 pm
In the BOL, use the Index tab, enter Local Temporary Tables. The right pane will display CREATE TABLE. Scroll down to TEMPORARY TABLES section. A local temporary table is only available to the current session. It is automatically dropped: 1. when the stored procedure completes or 2. when the session ends.
If they want the table to 'stick around' longer, make it a global temp table (##tablename) vice a local temp table (#tablename).
-SQLBill
November 21, 2005 at 10:07 am
Tim S. Your code did the job. Thanks.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply