Checking if table exist or not

Question

I am retrieving data from database using jdbc. In my code I am using 3-4 tables to get data. But sometimes if table is not present in database my code gives exception. How to handle this situation. I want my code to continue working for other tables even if one table is not present. Please help.

I have wrote a code like this

sql="select * from table"
now Result set and all.

If table is not present in database it give exception that no such table. I want to handle it. In this code I cannot take tables which are already present in advance . I want to check here itself if table is there or not.

Please do not mark it as a duplicate question. The link you shared doesnot give me required answer as in that question they are executing queries in database not through JDBC code

How can a table be not present? As a programmer when you construct a query or ORM handler you should know which tables are present in the database, isn't it ?? — Infamous, Oct 29 '17 at 07:10
capture the exception and process it as you wish. however, these exceptions are for that. — mr mcwolf, Oct 29 '17 at 07:20
@alfasin please donot mark it as duplicate. It is some what different from it — zaib7777, Oct 29 '17 at 07:24
@zaib7777 it looks exactly the same, what in your question is different? — Nir Alfasi, Oct 29 '17 at 07:29
@alfasin I have already edited my question to explain you why it is different and I am using Sybase which is much more different from mysql and INFORMATION_SCHEMA.TABLES don't work in it — zaib7777, Oct 29 '17 at 07:37
Next time make sure to tag your question with the relevant label... — Nir Alfasi, Oct 29 '17 at 07:38
I have tagged with a relevant label. You should make sure to ask someone before marking someone's question as duplicate — zaib7777, Oct 29 '17 at 07:39
which Sybase product (ASE? SQLAnywhere? IQ? Advantage?); the system schemas are different across these products — markp-fuso, Oct 29 '17 at 14:24
Possible duplicate of [How to only create table if it doesn't exist? Using "object\_id('table') IS NULL" doesn't work?](https://stackoverflow.com/questions/7974541/how-to-only-create-table-if-it-doesnt-exist-using-object-idtable-is-null) — Kostas C., Oct 30 '17 at 12:33

markp-fuso · Accepted Answer · 2017-10-29T16:04:26.587

For Sybase ASE the easiest/quickest method would consist of querying the sysobjects table in the database where you expect the (user-defined) table to reside:

select 1 from sysobjects where name = 'table-name' and type = 'U'

if a record is returned => table exists
if no record is returned => table does not exist

How you use the (above) query is up to you ...

return a 0/1-row result set to your client
assign a value to a @variable
place in a if [not] exists(...) construct
use in a case statement

If you know for a fact that there won't be any other object types (eg, proc, trigger, view, UDF) in the database with the name in question then you could also use the object_id() function, eg:

select object_id('table-name')

if you receive a number => the object exists
if you receive a NULL => the object does not exist

While object_id() will obtain an object's id from the sysobjects table, it does not check for the object type, eg, the (above) query will return a number if there's a stored proc named 'table-name'.

As with the select/sysobjects query, how you use the function call in your code is up to you (eg, result set, populate @variable, if [not] exists() construct, case statement).

So, addressing the additional details provided in the comments ...

Assuming you're submitting a single batch that needs to determine table existence prior to running the desired query(s):

-- if table exists, run query(s); obviously if table does not exist then query(s) is not run

if exists(select 1 from sysobjects where name = 'table-name' and type = 'U')
begin
     execute("select * from table-name")
end

execute() is required to keep the optimizer from generating an error that the table does not exist, ie, the query is not parsed/compiled unless the execute() is actually invoked

If your application can be written to use multiple batches, something like the following should also work:

# application specific code; I don't work with java but the gist of the operation would be ...
run-query-in-db("select 1 from sysobjects where name = 'table-name' and type = 'U'")
if-query-returns-a-row
then
    run-query-in-db("select * from table-name")
fi

Hey thanks for this. But to let you I can't use this. I have to check at runtime whether table exist or not. My code is such that it will make a table at runtime. and that tablename it will pass to sql statement as mentioned in my question. so, I think this is not the right solution for this particular problem. — zaib7777, Oct 29 '17 at 15:19
As I mentioned, the basic code can be used in many ways depending on your requirements; Kostas' example shows basically the same thing for a conditional `create table`; I suggest you update your question with an example of exactly what you want to do a) if the table exists and b) if the table does not exist — markp-fuso, Oct 29 '17 at 15:51
if table exist my code will fetch data from DB but incase if it doesnot exist my code should not stop processing it should continue ti process remaining code which is not dependent on this table which doesnot exist — zaib7777, Oct 29 '17 at 15:54
I've added to my answer to address your latest comment/example; I still recommend you update your question to be more specific as to what you want to do when the table does (not) exist; as currently written ... "I want to handle it" ... does not tell us **how** you want handle it; also keep in mind that folks coming into this question have better things to do with their time than reading through all the comments and then try to piece together what is actually desired (ie, consolidate all of your comments/details in the question) — markp-fuso, Oct 29 '17 at 16:06

Kostas C. · Answer 2 · 2017-10-29T07:48:01.927

1

This is the way of checking if the table exists and drop it:

IF EXISTS (
  SELECT 1
  FROM sysobjects
  WHERE name = 'a_table'
  AND type = 'U'
)
DROP TABLE a_table
GO

And this is how to check if a table exists and create it.

IF NOT EXISTS (
  SELECT 1
  FROM sysobjects
  WHERE name = 'a_table'
  AND type = 'U'
)
EXECUTE("CREATE TABLE a_table (
  col1 int not null,
  col2 int null
)")
GO

(They are different because in table-drop a temporary table gets created, so if you try to create a new one you will get an exception that it already exists)

edited Oct 29 '17 at 07:48

answered Oct 29 '17 at 07:33

Kostas C.

103
2
10

I am using sybase INFORMATION_SCHEMA.TABLES don't work in it – zaib7777 Oct 29 '17 at 07:35
I am updating the answer! – Kostas C. Oct 29 '17 at 07:40

score 0 · Answer 3 · answered Oct 29 '17 at 08:04

Before running the query which has some risk in table not existing, run the following sql query and check if the number of results is >= 1. if it is >= 1 then you are safe to execute the normal query. otherwise, do something to handle this situation.

SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')

I am no expert in Sybase but take a look at this,

exec sp_tables '%', '%', 'master', "'TABLE'"

Sybase Admin

Checking if table exist or not

3 Answers3