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

Question

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 = !tables.next();
return isEmpty;

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

Answer 1

Are you always checking databases created in the same way? If so you might be able to simply select from a subset of tables that you are familiar with to look for data.

You also might need to be concerned about static data perhaps added to a lookup table that looks like 'data' from a cursory glance, but might in fact not really be 'data' in an interesting sense of the term.

Can you provide any more information about the specific problem you are trying to tackle? I wonder if with more data a simpler and more reliable answer might be provided.

Are you creating these databases?
Are you creating them with roughly the same constructor each time? What kind of process leaves these guys hanging around, and can that constructor destruct?

There is certainly a meta data process to loop through tables, just through something a little more custom might exist.

Answer 2

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

SELECT COUNT(*) FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_TYPE] = <tabletype>

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:

http://msdn.microsoft.com/en-us/library/aa933204(SQL.80).aspx

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:

http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx

Answer 3

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( tables.next () ) {
    if( !"SYSTEM TABLE".equals( tables.getString( "table_type" ) ) ) {
        nonSystemTableCount++;
    }
}
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.

Answer 4

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