How can I detect a SQL table's existence in Java?

Question

How can I detect if a certain table exists in a given SQL database in Java?

Answer 1

Maybe catch the exception if a simple query to it fail?

Answer 2

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

In SQL Server for instance:

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

Answer 3

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

Googling information_schema.tables will help a lot.

Answer 4

Depending on the DB, you can do (MySQL)

SHOW TABLES

or (Oracle)

SELECT * FROM user_objects WHERE object_type = 'TABLE'

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

Answer 5

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

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

Answer 6

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

This method works transparently and is independent of the database engine. I think it queries information schema tables 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));
}

Answer 7

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

You can get the DatabaseMetaData instance by 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[])

Answer 8

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

See DatabaseMetaData.getTables

Answer 9

For MS Access:

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

Answer 10

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

select 1 from information_schema.tables where table_name = @tableName

Answer 11

Check this link: HOW TO: SQL and Java for details on how to connect to SQL Server database from Java database applications as well as C#.NET database applications. It also describes how to pass embedded SQL queries, calling stored procedures, pass parameter etc.