[ACCEPTED]-How can I detect a SQL table's existence in Java?-database

Accepted answer

Score: 43

You can use DatabaseMetaData.getTables() to get information about existing 4 tables.

This method works transparently and 3 is independent of the database engine. I 2 think it queries information schema tables 1 behind the scenes.

Edit:

Here is an example that prints all existing table names.

DatabaseMetaData md = connection.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

Score: 14

Use java.sql.DatabaseMetaData.getTables(null, null, YOUR_TABLE, null). If the table exists, you will get 1 a ResultSet with one record.

See DatabaseMetaData.getTables

Score: 2

For ALL ANSI-compliant databases: (mySQL, SQL 2 Server 2005/2008, Oracle, PostgreSQL, SQLLite, maybe 1 others)

select 1 from information_schema.tables where table_name = @tableName

Score: 1

This is not a language-specific, but a database-specific 3 problem. You'd query the metadata in the 2 database for the existence of that particular 1 object.

In SQL Server for instance:

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table]')
   AND type in (N'U')

Score: 1

Write a query that queries the table/view 3 that will list the tables (this is different 2 depending on DB vendor). Call that from 1 Java.

Googling information_schema.tables will help a lot.

Score: 1

Depending on the DB, you can do (MySQL)

SHOW TABLES

or 3 (Oracle)

SELECT * FROM user_objects WHERE object_type = 'TABLE'

or another thing for SQL Server. Cycle 2 through the results for MySQL or further 1 filter on the Oracle one.

Score: 1

Why not just see if it is in sysobjects 1 (for SQL Server)?

SELECT [name] FROM [sysobjects] WHERE type = 'U' AND [name] = 'TableName'

Score: 1

There is a JDBC feature, database vendor 3 independent - see [java.sql.DatabaseMetaData#getTables()][1]

You 2 can get the DatabaseMetaData instance by 1 calling java.sql.Connection#getMetaData()

[1]: http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String, java.lang.String, java.lang.String, java.lang.String[])

Score: 1

This is what worked for me for jdbc:derby:

//Create Staff table if it does not exist yet
String tableName = "STAFF";
boolean exists = conn.getMetaData().getTables(null, null, tableName, null).next();
if(!exists){
    s = conn.createStatement();
    s.execute("create table staff(lastname varchar(30), firstname varchar(30), position varchar(20),salary double,age int)");
    System.out.println("Created table " + tableName);
}

Note that 1 tableName has to be all caps.

Score: 0

For MS Access:

Select Count(*) From MSysObjects 
Where type=1 And name='your_table_name_here'

Source: stackoverflow.com

[ACCEPTED]-How can I detect a SQL table's existence in Java?-database

How to best store a timestamp or date in a database?

Why hibernate always call "update" statement after using "select" statement in MySQL?

How to create temporary procedures in MySQL?

What is a columnar database?

Should I add this new column to customers table or to a separate new table?

T-SQL String Replace

Should a database table have default values?

How to Delete all data from a table which contain self referencing foreign key

How do Relational Databases Work Under the Hood?

SQL Injection after removing all single-quotes and dash-characters