473,231 Members | 2,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

How to tell if a database table exists?

You'd think this would be the most basic sql query in the world but noooooo!
I've tried this:

on error resume next
strsql = "SELECT * FROM " & session("TablePrefix") & CurrentTable
SET rs = conn.execute(strsql)

tableExists = 0

if (Err.number = 0) then
tableExists = 1
end if

But it doesn't return an error if the table doesn't exist. I'm searching on
the internet and hitting these long complicatred solutions involving the
database "shema". Surely there is a simple way of telling with one line of
sql if a table exists or not??? Thanks!
Jul 19 '05 #1
7 9968
<snip>

I have found and implemented this solution from the internet:

<!-- #include file="adovbs.inc"-->
<%
Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "DSN=Library"
Set Rs = Conn.OpenSchema (adSchemaTables)

tableExists = 0

Do While Not Rs.EOF
if (rs("Table_Name") = CurrentTable) then
tableExists = 1
end if
Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing

Conn.Close
Set Conn = Nothing
%>
You must find on your system the include file adovbs.inc and copy it to the
directory where you ASP code is!

I'm just astonished and shaking my head in utter disbelief that there isn't
a simpler way of determining if a table exists! UGH!
Jul 19 '05 #2
Hi Simon,

Simon Wigzell wrote:
But it doesn't return an error if the table doesn't exist. I'm searching on
the internet and hitting these long complicatred solutions involving the
database "shema". Surely there is a simple way of telling with one line of
sql if a table exists or not??? Thanks!


With one line of SQL, I don't know, but with a few lines of vbscript....

set Cat = CreateObject("ADOX.Catalog")
Cat.activeConnection = conn
for each table in Cat.tables
if table.type="TABLE" then
if table.name = session("TablePrefix") & CurrentTable then
' do your thing....
end if
end if
next
set Cat = Nothing
HTH
Yan
Jul 19 '05 #4
Simon Wigzell wrote:
You'd think this would be the most basic sql query in the world but
noooooo! I've tried this:

on error resume next
strsql = "SELECT * FROM " & session("TablePrefix") & CurrentTable
SET rs = conn.execute(strsql)

tableExists = 0

if (Err.number = 0) then
tableExists = 1
end if

But it doesn't return an error if the table doesn't exist.
Yes it does. Your problem is that you've executed a statement that does not
raise an error between the statement that raises an error (the Execute
statement) and the statement that tests the Err object for the existence of
an error. When a statement executes with no error, the Err object is
cleared. Move the "tableExists = 0" line to before the Execute statement.
That will allow you to see the error.

Better yet, check the connection object's Errors collection, which will not
be affected by the execution of subsequent vbscript statements.

I'm
searching on the internet and hitting these long complicatred
solutions involving the database "shema". Surely there is a simple
way of telling with one line of sql if a table exists or not???
Thanks!


It's possible, but the implementation depends on the database you are using.
Jet databases have a MSysObjects table which can be queried for the database
schema. SQL Server has INFORMATION_SCHEMA views which can also be queried
for this information. I suspect Oracle databases have similar structures.
See:
http://www.aspfaq.com/show.asp?id=2112

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #5
PW

"Simon Wigzell" <si**********@shaw.ca> wrote in message
news:cIfAc.778765$oR5.348143@pd7tw3no...
You'd think this would be the most basic sql query in the world but

noooooo!
If you're using Access, try this ...
myTableName = "BLAH"
myFileExists = FALSE
mySQL = "SELECT name FROM MSysObjects WHERE type in (1, 4)"
rs6.open mySQL,mydsn
Do While NOT rs6.EOF
if rs6("name") = myTableName then
myFileExists = TRUE
end if
rs6.MoveNext
Loop
Jul 19 '05 #6
PW wrote:
"Simon Wigzell" <si**********@shaw.ca> wrote in message
news:cIfAc.778765$oR5.348143@pd7tw3no...
You'd think this would be the most basic sql query in the world but
noooooo!

If you're using Access, try this ...
myTableName = "BLAH"
myFileExists = FALSE
mySQL = "SELECT name FROM MSysObjects WHERE type in (1, 4)"
rs6.open mySQL,mydsn

This part is just silly:
******************************* Do While NOT rs6.EOF
if rs6("name") = myTableName then
myFileExists = TRUE
end if
rs6.MoveNext
Loop

*******************************

Change your sql to (in practice, I would use a saved parameter query instead
of dynamic sql):
mySQL = "SELECT count(*) FROM MSysObjects " & _
"WHERE type in (1, 4) AND [name] = '" & myTableName & "'"

Now, open rs6 and simply check whether or not rs6(0) contains 0.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #7
PW

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eY****************@TK2MSFTNGP11.phx.gbl...

This part is just silly:

Done ...
myTableName = "BLAH"
mySQL = "SELECT count(*) FROM MSysObjects WHERE type in (1, 4) AND [name] =
'" & myTableName & "'"
rs6.open mySQL,mydsn
if rs6(0) = 0 then
myFileExists = FALSE
else
myFileExists = TRUE
end if
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jonathan | last post by:
I am looking for a simple way to check if a database table exists. I keep getting advice to use "Try.. Catch" and other error handling methods, but I obviously don't want to have to display an...
3
by: konsu | last post by:
hello, i would like to write a batch file with all sql commands necessary to create a database, its tables, and populate them with initial data. would the experts please help me with these...
6
by: Clay Beatty | last post by:
When you create database diagrams in Enterprise Manager, the details for constructing those diagrams is saved into the dtproperties table. This table includes an image field which contains most of...
1
by: Michael D | last post by:
What are the pros and cons of the following two design methods ? (1) Using foreign keys to form a composite primary key of a child tables -- as in Example. POOR MAN'S ERD PROVIDED FOR SUMMARY...
2
by: adam | last post by:
hello What query shoul I send to SQL serwer ( in transact SQL language ) to check does some database exist on serwer ? It similar to problem "does some table exist in database" - resolve to it...
8
by: David | last post by:
Hi, Could someone please xplain how to add a field to an existing SQL table in VB.Net I have added the field in the Server Explorer and it shows up when I reload the program but I cannot...
3
by: Marty | last post by:
OK, I'm new at all of this and have decided to take on a project to learn. We have a fishing club that has 23 members. Each weak we go fishing in various places for 4 hours. Not together, usually 2...
4
by: qwedster | last post by:
Howdy folks! I am using stored procedure to see if a value exists in database table and return 0 if exists or else -1, in the following SQL queries. However how to check if a value (that is...
2
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.