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.

  • DATENAME(MONTH,GETADATE()) is returning numeric value of the month as '09'
  • How to pass XML from C# to a stored procedure in SQL Server 2008?
  • How to add meta data to every cell in all tables of a relational database?
  • ASP.Net insert data from Textbox to a database
  • What are the advantages of a query using a derived table(s) over a query not using them?
  • Cannot assign a default value to a local variable in SQL
  • 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).

  • How to export data from SQL Server 2008.2010 in DML (SQL script)?
  • SQL Server 2005 - Syncing development/production databases
  • How to delete rows in tables that contain foreign keys to other tables
  • Generate different random time in the given interval
  • Case in Select Statement
  • TSQL Merge 2 Ids into Temp Table
  • 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

    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

    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.

    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}" 
                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

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