Check if temporary table exists or not?

Question

Post reply

Check if temporary table exists or not?

Arun T Jayapal

Say Hey Kid

Points: 707
More actions
January 4, 2008 at 7:33 pm

#73945

2 different questions:
1. Can someone jot down the statements to check if a particular temporary table exists or not?
2. And what is the recommended way to check if an ordinary user created table exists or not...is the below recommended:
if exists (select * from sysobjects where name = 'mytesttable')
print 'exists';
else
print 'does not exist';

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 734434

January 4, 2008 at 8:31 pm

#765025

Don't use sysobjects, use information_schema.tables.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 2

Don't use either... use the functions built for this...

1. Check if temp table exists...

IF OBJECT_ID('TempDB..#temptablename','U') IS NOT NULL

PRINT 'Table Exists'

ELSE

PRINT 'Table Not Exists'

2. Same for permanent tables...

IF OBJECT_ID('dbo.permanenttablename','U') IS NOT NULL

PRINT 'Table Exists'

ELSE

PRINT 'Table Not Exists'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Concur...

2. Same for permanent tables...
IF OBJECT_ID('dbo.permanenttablename','U') IS NOT NULL PRINT 'Table Exists' ELSE PRINT 'Table Not Exists'

IF OBJECT_ID(' .dbo.permanenttablename','U') IS NOT NULL PRINT 'Table Exists' ELSE PRINT 'Table Not Exists'

Always include the DB name wherever it is necessary.Because it should reduce the unwanted confusions.

Otherwise Jeff Query looks fine.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

My proof read statement is,

IF OBJECT_ID('DBname.dbo.permanenttablename','U') IS NOT NULL PRINT 'Table Exists' ELSE PRINT 'Table Not Exists'

karthik

Arun T Jayapal Say Hey Kid Points: 707 More actions · Answer 5

In the documentation object_id should return an integer if it finds the table object. But if I do something like

if (object_id('tempdb..#tblTemp','u') > 0)

print 'exists'

else

print 'not exists'

...its not compiling...:ermm:

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

karthikeyan (1/7/2008)
Concur...
2. Same for permanent tables...
IF OBJECT_ID('dbo.permanenttablename','U') IS NOT NULL PRINT 'Table Exists' ELSE PRINT 'Table Not Exists'
IF OBJECT_ID(' .dbo.permanenttablename','U') IS NOT NULL PRINT 'Table Exists' ELSE PRINT 'Table Not Exists'
Always include the DB name wherever it is necessary.Because it should reduce the unwanted confusions.
Otherwise Jeff Query looks fine.

No... I disagree... 3 part naming convention is not necessary unless you are querying outside the current database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Arun T Jayapal (1/7/2008)
if (object_id('tempdb..#tblTemp','u') > 0) print 'exists' else
print 'not exists'
...its not compiling...:ermm:

Looks fine, and runs fine for me. What's the error that you're getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Loner SSC-Insane Points: 21279 More actions · Answer 8

Steve,

I always use SYSOBJECT but you suggested to use information_schema.tables. What's wrong with using SYSOBJECT?

Maybe I really need to beef up my DBA skills, I guess being a part time DBA still require a full time DBA knowledge.

Sergiy SSC Guru Points: 110206 More actions · Answer 9

Arun T Jayapal (1/7/2008)
In the documentation object_id should return an integer if it finds the table object. But if I do something like
if (object_id('tempdb..#tblTemp','u') > 0)
print 'exists'
else
print 'not exists'
...its not compiling...:ermm:

1st.

Must be a copy-paste problem.

Check what you've got in your QA after copying the code.

See if sign ">" is transferred correctly.

2nd.

Your condition is wrong.

Object_ID can be negative.

It must be:

[Code]

IF object_id('tempdb..#tblTemp','u') IS NOT NULL

print 'exists'

else

print 'not exists'

[/Code]

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 10

Loner (1/7/2008)
Steve,
I always use SYSOBJECT but you suggested to use information_schema.tables. What's wrong with using SYSOBJECT?
Maybe I really need to beef up my DBA skills, I guess being a part time DBA still require a full time DBA knowledge.

It's not a good idea to give every user rights to access system tables.

Even for reading only.

It's just one reason.

_____________
Code for TallyGenerator

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

Jeff Moden (1/4/2008)
Don't use either... use the functions built for this...
1. Check if temp table exists...
IF OBJECT_ID('TempDB..#temptablename','U') IS NOT NULL
PRINT 'Table Exists'
ELSE
PRINT 'Table Not Exists'
2. Same for permanent tables...
IF OBJECT_ID('dbo.permanenttablename','U') IS NOT NULL
PRINT 'Table Exists'
ELSE
PRINT 'Table Not Exists'

Your checks are not valid for SQL 7.0 and 2000. (This is the SQL Server 7,2000 T-SQL forum)

The following work in SQL 7.0, 2000, and 2005.

-- Check for temp table

if exists (

select *

from

tempdb.dbo.sysobjects o

where

o.xtype in ('U')and

o.id = object_id( N'tempdb..#temptablename')

)

begin

print 'Table exists'

end

else

begin

print 'Table does not exist'

end

-- Check for user table in current database

if objectproperty(object_id('authors'),'IsUserTable') = 1

begin

print 'Table exists'

end

else

begin

print 'Table does not exist'

end

Sergiy SSC Guru Points: 110206 More actions · Answer 12

Michael Valentine Jones (1/7/2008)
Your checks are not valid for SQL 7.0 and 2000. (This is the SQL Server 7,2000 T-SQL forum)

It's because undocumented parameter Jeff used.

Follow BOL prescriptions an you're OK:

[Code]IF OBJECT_ID('dbo.permanenttablename') IS NOT NULL

...

[/Code]

Parameter helps to understand if the object is a table or anything else.

But problem is if you have a view dbo.SomeName you cannot create a table dbo.SomeName anyway.

So, though that parameter could be useful in some cases I would not suggest to use it extensively. At least you need to understand what do you miss there.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 13

Michael Valentine Jones (1/7/2008)
Your checks are not valid for SQL 7.0 and 2000. (This is the SQL Server 7,2000 T-SQL forum)

Maybe not in SQL Server 7 because of the undocumented parameter I used (like Sergiy said)... but even with the extra parameter, the code works fine in 2k and 2k5 (or, at least it does on my servers).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 14

Sergiy (1/7/2008)
Michael Valentine Jones (1/7/2008)
Your checks are not valid for SQL 7.0 and 2000. (This is the SQL Server 7,2000 T-SQL forum)
It's because undocumented parameter Jeff used.
Follow BOL prescriptions an you're OK:
[Code]IF OBJECT_ID('dbo.permanenttablename') IS NOT NULL
...
[/Code]
Parameter helps to understand if the object is a table or anything else.
But problem is if you have a view dbo.SomeName you cannot create a table dbo.SomeName anyway.
So, though that parameter could be useful in some cases I would not suggest to use it extensively. At least you need to understand what do you miss there.

The parameter that Jeff used is not undocumented. It was introduced in SQL 2005 and is documented in the SQL 2005 BOL. That is why I said the checks are not valid for SQL 7.0 and 2000.

The problem with "IF OBJECT_ID('dbo.permanenttablename') IS NOT NULL" is that it cannot tell if the object is a table or some other kind of object. The code from my first post returns true only if they are actually tables.