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).



Answers

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

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

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( 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.

By : user19113


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