How to tell if a database table exists?

Discussion in 'ASP General' started by Simon Wigzell, Jun 17, 2004.

  1. 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!
    Simon Wigzell, Jun 17, 2004
    #1
    1. Advertising

  2. How to tell if a database table exists? More

    <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!
    Simon Wigzell, Jun 17, 2004
    #2
    1. Advertising

  3. Simon Wigzell

    CJM Guest

    CJM, Jun 17, 2004
    #3
  4. Simon Wigzell

    Yan Roosens Guest

    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
    Yan Roosens, Jun 17, 2004
    #4
  5. 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.
    Bob Barrows [MVP], Jun 17, 2004
    #5
  6. Simon Wigzell

    PW Guest

    "Simon Wigzell" <> 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
    PW, Jun 17, 2004
    #6
  7. PW wrote:
    > "Simon Wigzell" <> 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.
    Bob Barrows [MVP], Jun 17, 2004
    #7
  8. Simon Wigzell

    PW Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:eYvQ%...
    >
    > 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
    PW, Jun 17, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. UJ
    Replies:
    0
    Views:
    340
  2. Thorsten Meininger
    Replies:
    1
    Views:
    450
    Sudsy
    Oct 13, 2004
  3. Totan
    Replies:
    0
    Views:
    914
    Totan
    Apr 17, 2006
  4. Ulf Meinhardt
    Replies:
    8
    Views:
    6,109
  5. Jonathan

    How can I check if a Database Table exists?

    Jonathan, Apr 4, 2004, in forum: ASP General
    Replies:
    2
    Views:
    419
    Bob Barrows
    Apr 4, 2004
Loading...

Share This Page