0

I want to do the same as in the answers to this question: Check if table exists but I want to check if any table exist, not just a particular table and not iterating through table names, I mean if there's any table at all in the database. According to the API, the third argument of getTables(),

tableNamePattern - a table name pattern; must match the table name as it is stored in the database

So it seems the tableName should match exactly in the database. Is there a better way to find if any table exist in the database? Or I really have to loop through an array of table names and call:

getConnection().getMetaData().getTables(null, null, "table", null).next();

each iteration?

Community
  • 1
  • 1
lightning_missile
  • 2,821
  • 5
  • 30
  • 58
  • The answer from the link you provided (the choosen one) has what you are asking. `meta.getTables(null, null, null, new String[] "TABLE"});` Then you just iterate through it. – Jorge Campos Jul 20 '14 at 03:13
  • @JorgeCampos Actually the third parameter should be `"%"`. JDBC isn't entirely clear if `null` is supposed to work for this parameter and some drivers won't work if null is specified for this parameter. – Mark Rotteveel Jul 20 '14 at 13:59
  • The accepted answer in the question you link to will return all tables (at least: in some, but not all drivers, otherwise see my previous comment). – Mark Rotteveel Jul 20 '14 at 14:01
  • @MarkRotteveel sounds like you need to add your comment to the accepted answer on the duplicate that you suggested. – Erwin Bolwidt Jul 20 '14 at 16:26

2 Answers2

2

You can determine if any tables exist by querying the table INFORMATION_SCHEMA.TABLES:

select *
from INFORMATION_SCHEMA.TABLES;

If you want to look for a particular pattern in a name, you can add a where clause:

WHERE table_name like YOURPATTERNHERE
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

To compliment a little more, use IF EXISTS() like this:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%MY TABLE%')
BEGIN
     --Do Something here
END
Hiram
  • 2,679
  • 1
  • 16
  • 15