Determine if a temproary Table still exists

Does anyone know how to determine if a Temporary Table was dropped already o
r
not? If the Table still exists?John
if object_id('tempdb.#tt') is not null
print 'yes'
else
print 'no'
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:1B5BAFB9-D573-4841-8FE3-2C1B1D0EBBEC@.microsoft.com...
> Does anyone know how to determine if a Temporary Table was dropped already
> or
> not? If the Table still exists?|||How many paths does your code have where you wouldn't know whether the table
has already been dropped? Any reason you would drop the table before the
last line in the procedure?
Anyway,
IF OBJECT_ID('tempdb..#tablename') IS NOT NULL
PRINT '...still exists...'
ELSE
PRINT 'It''s gone...'
A
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:1B5BAFB9-D573-4841-8FE3-2C1B1D0EBBEC@.microsoft.com...
> Does anyone know how to determine if a Temporary Table was dropped already
> or
> not? If the Table still exists?|||I have tried both suggestions, and neither worked because in the tempdb, the
temp table name is appended to by SQL to make it unique in case multiple
instances are created. There is only one branch where I am checking this, an
d
it is in an error section.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How many paths does your code have where you wouldn't know whether the tab
le
> has already been dropped? Any reason you would drop the table before the
> last line in the procedure?
> Anyway,
> IF OBJECT_ID('tempdb..#tablename') IS NOT NULL
> PRINT '...still exists...'
> ELSE
> PRINT 'It''s gone...'
> A
>
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:1B5BAFB9-D573-4841-8FE3-2C1B1D0EBBEC@.microsoft.com...
>
>|||Select tablename
from tempdb.information_schema.tables
where tablename like '[#tablename]%'
Note that I am searching the tempdb database...
You must use the like comparison and wildcard (%) because a unique idetifyer
is appended to the end of the temporary tables in tempdb.
RMG66
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:1B5BAFB9-D573-4841-8FE3-2C1B1D0EBBEC@.microsoft.com...
> Does anyone know how to determine if a Temporary Table was dropped already
or
> not? If the Table still exists?|||I apologize, this does work. If you wrap it in OBJECT_NAME it does not work.
Thanks for the info. i.e. SELECT OBJECT_NAME(OBJECT_ID('tempdb..#tablename')
)
I was just checking to see if it returned a result, but it comes back null.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How many paths does your code have where you wouldn't know whether the tab
le
> has already been dropped? Any reason you would drop the table before the
> last line in the procedure?
> Anyway,
> IF OBJECT_ID('tempdb..#tablename') IS NOT NULL
> PRINT '...still exists...'
> ELSE
> PRINT 'It''s gone...'
> A
>
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:1B5BAFB9-D573-4841-8FE3-2C1B1D0EBBEC@.microsoft.com...
>
>|||> because in the tempdb, the
> temp table name is appended to by SQL to make it unique in case multiple
> instances are created.
Yes, understood, I think we're aware of how #temp tables are named in
tempdb.
Note that Uri's example has a typo, tempdb.#tt should have been tempdb..#tt
...
However, I don't think you tried my suggestion. Just like CREATE TABLE #t1
creates #t1________________blahblah in tempdb, so does any other reference
to #t1, e.g. UPDATE #t1, DELETE #t1, DROP TABLE #t1,
OBJECT_ID('tempdb..#t1'), etc. etc. Otherwise, you would have to know the
suffix in order to do anything with a #table once it is created, right? Try
this:
CREATE TABLE #t1(id INT);
IF OBJECT_ID('tempdb..#t1') IS NOT NULL
PRINT 'T1 exists...';
ELSE
PRINT 'T1 does not exist...';
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
PRINT 'T2 exists...';
ELSE
PRINT 'T2 does not exist...';
DROP TABLE #t1;
On my server, I get:
--
T1 exists...
T2 does not exist...
Let me know if you get something different, and provide a repro.
BTW, start with the simple steps, instead of jamming the suggestion right
into a complex stored procedure with error handling, as I am guessing if the
above didn't work correctly than you were incorrect about your assumption
that the error was handled before or after the #temp table was actually
created.|||>I apologize, this does work. If you wrap it in OBJECT_NAME it does not
>work.
That's right, because OBJECT_NAME assumes that the incoming ID belongs to
the CURRENT SCOPED DATABASE.

> Thanks for the info. i.e. SELECT
> OBJECT_NAME(OBJECT_ID('tempdb..#tablename'))
> I was just checking to see if it returned a result, but it comes back
> null.
Why do you need the OBJECT_NAME to do this? Take that wrapper off of your
check...|||This will not work, because the check in INFORMATION_SCHEMA does not care
which session #tablename belongs to. This is the beauty of using
OBJECT_ID('tempdb..#tablename') in your own session. It cannot be
with the same #tablename in any other session.
To prove that it won't work, open two query editors in Query Analyzer (e.g.
two sessions/SPIDs):
In Window #1 run this, and leave the window open:
CREATE TABLE #foo(id INT)
INSERT #foo SELECT 1
Now in Window #2:
IF EXISTS
(
SELECT 1
FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '#foo%'
AND TABLE_TYPE = 'BASE TABLE'
)
BEGIN
PRINT 'dropping table'
DROP TABLE #foo
END
I get:
dropping table
Server: Msg 3701, Level 11, State 5, Line 5
Cannot drop the table '#foo', because it does not exist in the system
catalog.
OOPS! This is because the EXISTS comes back as true, even though my current
session does not hold such a table.
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:%231exmoMAGHA.3928@.tk2msftngp13.phx.gbl...
Third times a charm.
"routine_name" should be "table_name"
As you can see I use similar code to drop temporary Stored Procedures.
John
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:upepalMAGHA.1032@.TK2MSFTNGP11.phx.gbl...
Made a mistake in my example it should be:
if exists(select * from tempdb.information_schema.tables where
routine_name like '#tablename%' and table_type = 'base table')
begin
Print 'Dropping table [#tablename]'
drop table #tablename
end
go
John
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:un$yWiMAGHA.356@.TK2MSFTNGP12.phx.gbl...
Here is an example I use.
if exists(select * from tempdb.information_schema.routines where
routine_name like '#tablename%' and routine_type = 'base table')
begin
Print 'Dropping table [#tablename]'
drop table #tablename
end
go
John
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eu8TzcMAGHA.3928@.tk2msftngp13.phx.gbl...

> Select tablename
> from tempdb.information_schema.tables
> where tablename like '[#tablename]%'
> Note that I am searching the tempdb database...
> You must use the like comparison and wildcard (%) because a unique
idetifyer
> is appended to the end of the temporary tables in tempdb.
> RMG66
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:1B5BAFB9-D573-4841-8FE3-2C1B1D0EBBEC@.microsoft.com...
already
> or
>|||Further to my previous comment, if you really want to get your object_name
back, you can say:
EXEC('USE Tempdb; SELECT OBJECT_NAME(OBJECT_ID(''tempdb..#foo''));');
Your locally scoped #foo will still return the correct object_id, and
dynamically using tempdb will return the correct object_name.
But again, this is overkill, and unnecessary just to check existence. The
first sample I posted does this just fine...
A
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:FD70FD84-8153-4B32-80C4-0CD4BFD62D7C@.microsoft.com...
>I apologize, this does work. If you wrap it in OBJECT_NAME it does not
>work.
> Thanks for the info. i.e. SELECT
> OBJECT_NAME(OBJECT_ID('tempdb..#tablename'))
> I was just checking to see if it returned a result, but it comes back
> null.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>