Using SQL query to determine if a table exists

Question

Guys is there any other way to determine a table exists other than below

1. select count(*) from <table> where rownum =1
2. select * from user_table where table_name=<table>

kindly let me know the best way to check whether a table exists using oracle sql.

Thanks for the answer , my requirement is to check from the first date of current month ie 01/12/2010 with table name in the format suresh_20101201 exists in the database, if not then it should check for table suresh_20101202 and thereon till suresh_20101231 . is it possible to do in oracle sql query.

Answer 1

You can do this (in oracle, in mssql there is a bit different):

select count(*)
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'your_table_name';

Answer 2

In most sql servers there is a system domain where you can query for a table's existence. It's highly implementation specific though. For example, in recent versions of MySql:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  AND table_name LIKE 'whatever'