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.

  • T-SQL BETWEEN problem max value first
  • sqlcmd - How to display all the set option setting values
  • Would like to return a fake row if there is no match to my pair (for a year)
  • CREATE VIEW must be the only statement in the batch
  • How to get the connection String from a database
  • I need a mechanism for locating SQL server instances
  • 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).

  • SQL Server getting arithmetic overflow error converting vachar to data type numeric
  • Recall stored case in TSQL
  • SQL Query to Excel in C#
  • Is this the right order to clear SqlDataAdapter and Sql Command Parameters and close the connection?
  • Update column value of mutiple tables, only 1 row affected
  • Using IsNull or select COALESCE in Linq..?
  • 5 Solutions collect form web for “How can I generically detect if a database is 'empty' from Java”

    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:

    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.

    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.

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

    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}" 
        <!-- Main: --> 
        <target name="main" depends="run_sql_query"/>   

    For more details, please refer to ANT:

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.