How can I generically detect if a database is 'empty' from Java


Can anyone suggest a good way of detecting if a database is empty from Java (needs to support at least Microsoft SQL Server, Derby and Oracle)?

By empty I mean in the state it would be if the database were freshly created with a new create database statement, though the check need not be 100% perfect if covers 99% of cases.

My first thought was to do something like this...

tables = metadata.getTables(null, null, null, null);
Boolean isEmpty = !;
return isEmpty;

...but unfortunately that gives me a bunch of underlying system tables (at least in Microsoft SQL Server).


In Oracle, at least, you can select from USER_TABLES to exclude any system tables.

By : Mpvvliet

There are some cross-database SQL-92 schema query standards - mileage for this of course varies according to vendor


Support for these varies by vendor, as does the content of the columns for the Tables view. SQL implementation of Information Schema docs found here:

More specifically in SQL Server, sysobjects metadata predates the SQL92 standards initiative.

SELECT COUNT(*) FROM [sysobjects] WHERE [type] = 'U'

Query above returns the count of User tables in the database. More information about the sysobjects table here:

By : stephbu

I don't know if this is a complete solution ... but you can determine if a table is a system table by reading the table_type column of the ResultSet returned by getTables:

int nonSystemTableCount = 0;
tables = metadata.getTables(null, null, null, null);
while( () ) {
    if( !"SYSTEM TABLE".equals( tables.getString( "table_type" ) ) ) {
boolean isEmpty = nonSystemTableCount == 0;
return isEmpty;

In practice ... I think you might have to work pretty hard to get a really reliable, truly generic solution.

By : user19113

This video can help you solving your question :)
By: admin