Check if a table exist in the SQL DB using JdbcTemplate and T-SQL

Question

I am struggling with checking if a table exist in the DB. What I have done so far is as follows:

public boolean isTableExist(String tableName) {
    JdbcTemplate jdbc = getJdbcTemplate();
    String query =
        "IF (OBJECT_ID(?) IS NOT NULL ) "
            + "BEGIN "
            + "  PRINT 1 "
            + "  RETURN "
            + "END "
            + "ELSE "
            + "BEGIN "
            + "  PRINT 0 "
            + "  RETURN "
            + "END";

    Integer result = jdbc.queryForObject(query, Integer.class, tableName);
    return result == 1 ? true : false;
  }

Output (Error):

PreparedStatementCallback; uncategorized SQLException for SQL [IF (OBJECT_ID(?) IS NOT NULL ) BEGIN PRINT 1 RETURN END ELSE BEGIN
PRINT 0 RETURN END]; SQL state [null]; error code [0]; The statement did not return a result set.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

Great job editing the answer with the solution. Thank you! – zedfoxus Jun 09 '20 at 16:51 — zedfoxus, Jun 09 '20 at 16:51
Thank you, sir, for your help @zedfoxus – BetaDev Jun 09 '20 at 16:54 — BetaDev, Jun 09 '20 at 16:54

score 2 · Accepted Answer · edited Jun 09 '20 at 16:47

You can also run a query like so:

select count(*)
from information_schema.tables
where table_name = 'yourtablename'
    -- optionally this too
    -- and table_schema = 'dbo';

If you get zero, table doesn't exist.

Based on jdbctemplate count queryForInt and pass multiple parameters answer, it seems like you might have to use something like this once you store the query

jdbc.queryForObject(
    "select count(*) from information_schema.tables where table_name = ?"
    , new Object[] { tableName }
    , Integer.class
);

Update: The problem has been resolved with the help of this post and the final solution based on the above info is:

String query =
        "select count(*) "
            + "from information_schema.tables "
            + "where table_name = ? and table_schema = 'dbo'";
    Integer result = jdbc.queryForObject(query, Integer.class, tableName);

Check if a table exist in the SQL DB using JdbcTemplate and T-SQL

1 Answers1