Article: Using the Information Schema Views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Using the Information Schema Views

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-08-02 : 13:31:57

Keith writes "Help!!!!! I am trying to write code that will do a select statement from a table (if it exists). I do not know if the table exists or not however. Is there a SQL statement that will determine if the table exists or not? Thank you, Keith" Yes Keith, there certainly is and it's pretty easy to do. Two lines of code.

Article Link.

perlpunk
Starting Member

2 Posts

Posted - 2005-02-18 : 13:57:51

Long story short I needed to find out if a temp table exists because Classic ASP is a pile of crap, so here is really the only way I found.

If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects

Thanks for sql-server-performance.com for that answer.

-Hap

------
Digital Propulsion Labs
www.digitalpropulsionlabs.com
Denver, CO

ThomasK67
Starting Member

5 Posts

Posted - 2007-02-09 : 10:48:29

This is exactly what I needed to know. Thanks!

I've never worked with Classic ASP, but ASP.NET is very easy to pick up, especially if you know object-oriented programming. Really really easy if you know C++ and you're going to be doing ASP.NET in C#.

Thanks!
Thomas

ThomasK67
Starting Member

5 Posts

Posted - 2007-02-09 : 11:26:48

... except I get the message

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

I'm talking to my dba about it, I'm sure it's something he can fix fairly easily

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 11:40:02

You may find this useful:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67736[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ThomasK67
Starting Member

5 Posts

Posted - 2007-02-09 : 11:49:08

Thankya for the info

My dba sent me this:

if exists (select * from tempdb.dbo.sysobjects where name like '#THETEMPTABLE%')
DROP TABLE #THETEMPTABLE
GO

works groovy

thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 12:38:00

quote:
Originally posted by ThomasK67

Thankya for the info

My dba sent me this:

if exists (select * from tempdb.dbo.sysobjects where name like '#THETEMPTABLE%')
DROP TABLE #THETEMPTABLE
GO

works groovy

thanks!

One problem with that code is that is does not check to make sure it is a temp table, and not some other type of temporary object, like a procedure or function.

Another problem is that it does not look for an exact match, so it may find another table with a longer name.

The code from the F_TEMP_TABLE_EXISTS function that Harsh Athalye posted a link to does not have those problems, so this would be a better way to do it.


if exists (
	select  *
	from
		tempdb.dbo.sysobjects o
	where
		o.xtype in ('U')	and
		o.id = object_id( N'tempdb..#THETEMPTABLE' )
	)
drop table #THETEMPTABLE

CODO ERGO SUM

ThomasK67
Starting Member

5 Posts

Posted - 2007-02-09 : 17:12:46

Hey, thanks for the info... I'll look at it.

Points taken, though in this specific case neither would be a problem. However, specificity in coding can prevent future problems, so I'll try your method.

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 18:14:02

quote:
Originally posted by ThomasK67

Hey, thanks for the info... I'll look at it.

Points taken, though in this specific case neither would be a problem. However, specificity in coding can prevent future problems, so I'll try your method.

Thanks!

That is not the only possible problem of the other code. If would also find a temp table with the same name created on another connection, and get an error on the drop statement.

CODO ERGO SUM

ThomasK67
Starting Member

5 Posts

Posted - 2007-02-09 : 18:22:26

Hmm, interesting. I had understood otherwise from this page:

http://www.programmers-corner.com/article/76

Thanks for the input!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 18:41:58

quote:
Originally posted by ThomasK67

Hmm, interesting. I had understood otherwise from this page:

http://www.programmers-corner.com/article/76

Thanks for the input!

There is nothing in that article that addresses the problem I described with the code you posted from your DBA.

1. Open two Query Analyzer windows connected to the same server.
2. Execute this code in Windows 1:
create table #myTemp ( P int )
3. Execute this code in Window 2:
if exists(select * from tempdb.dbo.sysobjects where name like '#myTemp%')
drop table #myTemp

You will get this error:
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#myTemp', because it does not exist in the system catalog.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources