determine if temp table exists

I need to determine a way to search if a tempory table exist before atteptin
g
to read a record from it.
The only way I know of is to perform the following:
if exists(select * from tempdb..sysobjects where name like '#tablename%'
and type='U')
However, if there are multiple sessions on the same machine, this may pass
but the current session doesn't have an existing temporary table. Is there
a
way to determine that a temporary table listed in sysobjects is for the
current session?Try:
IF OBJECT_ID('tempdb..#tablename') IS NOT NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:12A99761-D2FD-4CE8-A6C5-DFA8673B5D9D@.microsoft.com...
> I need to determine a way to search if a tempory table exist before
attepting
> to read a record from it.
> The only way I know of is to perform the following:
> if exists(select * from tempdb..sysobjects where name like '#tablename%'
> and type='U')
> However, if there are multiple sessions on the same machine, this may pass
> but the current session doesn't have an existing temporary table. Is
there a
> way to determine that a temporary table listed in sysobjects is for the
> current session?|||if object_id('tempdb..#tmp','U') is not null
print('#tmp exists')
else
print('#tmp does not exist')
-oj
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:12A99761-D2FD-4CE8-A6C5-DFA8673B5D9D@.microsoft.com...
>I need to determine a way to search if a tempory table exist before
>attepting
> to read a record from it.
> The only way I know of is to perform the following:
> if exists(select * from tempdb..sysobjects where name like '#tablename%'
> and type='U')
> However, if there are multiple sessions on the same machine, this may pass
> but the current session doesn't have an existing temporary table. Is
> there a
> way to determine that a temporary table listed in sysobjects is for the
> current session?

Determine if a trigger exists.

I need to know how to determine if a specific trigger exists a on table and
if it does not i need to create it. Is this possible?
ThanksHi,
You may be able to use the following statement, but check the BOL
documentation for OBJECT_ID() because I'm not totally sure. Perhaps once
you've checked it out (or tested it) you can let the rest of us know how you
got along?
IF OBJECT_ID('dbname.owner.triggername') IS NULL
CREATE TRIGGER ...
Cheers,
Robert
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:684C9984-6AB3-41B2-8752-CB45BD8F00E5@.microsoft.com...
>I need to know how to determine if a specific trigger exists a on table and
> if it does not i need to create it. Is this possible?
> Thanks|||Hi, Robert
CREATE TRIGGER must be the first statement in a batch, so using it in
an IF statement won't work. There are (at least) two ways of dealing
with this:
a) drop the trigger if it exists and then create it:
IF OBJECT_ID('triggername') IS NOT NULL
DROP TRIGGER triggername
GO
CREATE TRIGGER ...
b) create the trigger if it doesn't exists, using EXEC('...'):
IF OBJECT_ID('triggername') IS NULL
EXEC('CREATE TRIGGER ...')
I preffer the first way, because:
1. By re-creating the trigger, I am sure that I have the correct
version of the trigger.
2. Using EXEC with a string means that I have to replace any
apostrophes in the trigger definition with double apostrophes.
Razvan|||Thanks for the help, I will try this today.
Thanks again!
"Razvan Socol" wrote:

> Hi, Robert
> CREATE TRIGGER must be the first statement in a batch, so using it in
> an IF statement won't work. There are (at least) two ways of dealing
> with this:
> a) drop the trigger if it exists and then create it:
> IF OBJECT_ID('triggername') IS NOT NULL
> DROP TRIGGER triggername
> GO
> CREATE TRIGGER ...
> b) create the trigger if it doesn't exists, using EXEC('...'):
> IF OBJECT_ID('triggername') IS NULL
> EXEC('CREATE TRIGGER ...')
> I preffer the first way, because:
> 1. By re-creating the trigger, I am sure that I have the correct
> version of the trigger.
> 2. Using EXEC with a string means that I have to replace any
> apostrophes in the trigger definition with double apostrophes.
> Razvan
>

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:
>