How to Check to See If Table Exists 5

[cghoga]

Greetings,

What is the best way to check to see if a temp table exists in SQL 2000?

I have a step that creates the table and would like to programatically handle the drop/create.

Thanks for your help,

Cary

 

[SQLSister]

{code]
if exists (select * from dbo.sysobjects where id = object_id(N'yourtablename') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
--do something here
end
[/code]


"NOTHING is more important in a database than integrity." ESquared

 

[TysonLPrice]

I use:

if object_id('tempdb..##Temp') is not null drop table ##Temp

 

[SQLDenis]

http://sqlservercode.blogspot.com/2006/02/check-if-temporary-table-exists.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ousoonerjoe]

The following example is what I used to look for a temp table:

SELECT ClaimID, FileNumber, DateOpened, Division
INTO Bull
FROM Claims
WHERE ClaimID < 10000


SELECT * FROM sysobjects
WHERE Name LIKE 'bull'

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ca8msm]

The following example is what I used to look for a temp table:

SELECT ClaimID, FileNumber, DateOpened, Division
INTO Bull
FROM Claims
WHERE ClaimID < 10000


SELECT * FROM sysobjects
WHERE Name LIKE 'bull'

That makes the assumption that no-one ever creates something like this:

create procedure bull
as
select id from anothertable

If they do, your logic will fail. Also, your code above doesn't actually create a temporary table; it creates a permanent one.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ousoonerjoe]

SELECT * FROM sysobjects
WHERE Name LIKE 'bull'

doesn't care what the object type is. It only cares that there is an object by that name. No two objects can have the same name. However, SQLSister, once again, has a much cleaner and accurate way to get there.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLDenis]

>>No two objects can have the same name


we are talking about TEMP tables here!!!!

run this code in two different windows

create table #temp(id int)

runs without a problem

SQLSisters code won't work either

I gave the link before but here it is again:

http://sqlservercode.blogspot.com/2006/02/check-if-temporary-table-exists.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Sorry. Dennis is right, I didn't notice the temp table part.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

create procedure #temp
as
select a=1
GO
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL drop table #temp

create table #temp (a int)

 

[ESquared]

sorry, messed up. this will show the problem.

create procedure #temp
as
select a=1
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL drop table #temp
create table #temp (a int)

best to use the undocumented second parameter

 

[cghoga]

Thanks to everyone for the great response!