CODEWITHSUNDEEP
javasql-serverdatabaseoracle-databasederby
Matt Sheppard
Matt Sheppard

Reputation: 118053

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

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

Upvotes: 1

Views: 3114

Answers (5)

Naor Bar
Naor Bar

Reputation: 2209

I could not find a standard generic solution, so each database needs its own tests set.

For Oracle for instance, I used to check tables, sequences and indexes:

select count(*) from user_tables
select count(*) from user_sequences
select count(*) from user_indexes

For SqlServer I used to check tables, views and stored procedures:

SELECT * FROM sys.all_objects where type_desc in ('USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW')

The best generic (and intuitive) solution I got, is by using ANT SQL task - all I needed to do is passing different parameters for each type of database.

i.e. The ANT build file looks like this:

<project name="run_sql_query" basedir="." default="main">
    <!-- run_sql_query: --> 
    <target name="run_sql_query">
        <echo message="=== running sql query from file ${database.src.file}; check the result in ${database.out.file} ==="/>
        <sql classpath="${jdbc.jar.file}" 
            driver="${database.driver.class}" 
            url="${database.url}" 
            userid="${database.user}" 
            password="${database.password}" 
            src="${database.src.file}"
            output="${database.out.file}"
            print="yes"/>
    </target>

    <!-- Main: --> 
    <target name="main" depends="run_sql_query"/>   
</project>

For more details, please refer to ANT:

https://ant.apache.org/manual/Tasks/sql.html

Upvotes: 0

Mpvvliet
Mpvvliet

Reputation: 11

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

Upvotes: 0

stephbu
stephbu

Reputation: 5091

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

Upvotes: 2

user19113
user19113

Reputation: 1228

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.

Upvotes: 1

Nathan Feger
Nathan Feger

Reputation: 19506

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.

Upvotes: 0

Related Questions