How to check table exist or not exist

Question

How to check table is there or not?

Using VB 6.0

cmd.CommandText = "drop table t1"
cmd.Execute

Above code is working fine, but if table is not exist then it showing “table does not exit”

How to check table exist or table not exist?

Need VB CODE help?

Answer 1

If you just want to drop the table without throwing an error message, you can use the following SQL if you're using MySQL.

DROP TABLE t1 IF EXISTS

Other databases have a similar feature, but the syntax is different. To do the same in MSSQL:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1') DROP TABLE t1;

Although that looks very ugly.. there must be a better syntax to get the same result.

Answer 2

You'd be better off checking for existance of the table concerned, rather than trying to drop it.

The SQL syntax is dependent on the database server/engine you're using, but for Sql Server you could use something like:

Sql Server 2000:

SELECT 1 as Exists FROM sysobjects WHERE name = 't1'

Sql Server 2005/2008:

SELECT 1 as Exists FROM sys.objects WHERE name = 't1'

You can then use VB like:

Dim rs as Recordset
Dim iExists as Integer

rs = cmd.Execute
On Error Goto DoesNotExist
rs.MoveFirst
iExists = CInt(rs!Exists)
DoesNotExist:
If iExists = 1 Then
 ' Put code here for if the table exists
Else
 ' Put code here for if the table does not exist
End If

Note: This code needs tidying up and "productionising" =) (i.e. I haven't actually tested that it works as I don't have VB6 on this machine)

Answer 3

For a Jet MDB (and perhaps generically for many OLEDB Providers) you can use an approach like:

Private Sub Main()
    Dim cnDB As ADODB.Connection

    Set cnDB = New ADODB.Connection
    cnDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Jet OLEDB:Engine Type=5;Data Source='sample.mdb'"

    'Check presence of table --------------
    Dim rsSchema As ADODB.Recordset

    Set rsSchema = _
        cnDB.OpenSchema(adSchemaColumns, _
                        Array(Empty, Empty, "t1", Empty))
    If rsSchema.BOF And rsSchema.EOF Then
        MsgBox "Table does not exist"
    Else
        MsgBox "Table exists"
    End If
    rsSchema.Close
    Set rsSchema = Nothing
    '--------------------------------------

    cnDB.Close
End Sub