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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using the Information Schema Views		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 08/02/2000 :  13:31:57  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

ScottMitchell
Starting Member

USA
6 Posts
Posted - 08/04/2000 :  01:27:11  Show Profile  Visit ScottMitchell's Homepage  Reply with Quote
More info on the sysobjects table

For more info on the sysobjects table check out these resources:

http://www.4guysfromrolla.com/webtech/090899-1.shtml
http://www.4guysfromrolla.com/webtech/sqlguru/q0830992.shtml

Happy Programming!
Go to Top of Page

ScottMitchell
Starting Member

USA
6 Posts
Posted - 08/05/2000 :  04:26:35  Show Profile  Visit ScottMitchell's Homepage  Reply with Quote
Another great article on the topic....

Listing the Tables and Columns in a Database
http://www.4guysfromrolla.com/webtech/101799-1.shtml
Go to Top of Page

sqlguru
SQLTeam CoFounder

USA
20 Posts
Posted - 08/05/2000 :  19:42:36  Show Profile  Visit sqlguru's Homepage  Reply with Quote
A quick and dirty way to tell if a table exists...

use the builtin object_id('[db.][owner.]objname') function. If it returns a non-null value, then the object exists.

I use this little bit o' code all the time:

IF OBJECT_ID('myproc') IS NOT NULL
    DROP PROC myproc
GO

when I'm working on a stored proc, so I can drop the old one and recreate it with a minimum of fuss.

object_id is sensitive to which database you're in, so make sure you're in the database that contains the object or fully qualify the object name.
Go to Top of Page

perlpunk
Starting Member

USA
2 Posts
Posted - 02/18/2005 :  13:57:51  Show Profile  Visit perlpunk's Homepage  Reply with Quote
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
Go to Top of Page

ThomasK67
Starting Member

5 Posts
Posted - 02/09/2007 :  10:48:29  Show Profile  Reply with Quote
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
Go to Top of Page

ThomasK67
Starting Member

5 Posts
Posted - 02/09/2007 :  11:26:48  Show Profile  Reply with Quote
... 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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts
Posted - 02/09/2007 :  11:40:02  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
You may find this useful:

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

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

ThomasK67
Starting Member

5 Posts
Posted - 02/09/2007 :  11:49:08  Show Profile  Reply with Quote
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!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts
Posted - 02/09/2007 :  12:38:00  Show Profile  Reply with Quote
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
Go to Top of Page

ThomasK67
Starting Member

5 Posts
Posted - 02/09/2007 :  17:12:46  Show Profile  Reply with Quote
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!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts
Posted - 02/09/2007 :  18:14:02  Show Profile  Reply with Quote
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
Go to Top of Page

ThomasK67
Starting Member

5 Posts
Posted - 02/09/2007 :  18:22:26  Show Profile  Reply with Quote
Hmm, interesting. I had understood otherwise from this page:

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

Thanks for the input!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts
Posted - 02/09/2007 :  18:41:58  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000