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'

Answer 3

You need to ask your server's system catalog. Not sure what database you meant but for SQL Server it would be:

select * from sys.tables where name='your-table-name-'

Answer 4

Used this in Oracle SQL Developer:

SELECT COUNT(*) FROM DUAL WHERE EXISTS (
    SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'myschema' AND OBJECT_NAME = 'your_table_name')

This will return either a 0 or 1 if your table exists or not in the ALL_OBJECTS records.

Answer 5

Below query can be triggered to Oracle for checking whether any Table present in DB or not:

SELECT count(*) count FROM dba_tables where table_name = 'TABLE_NAME'

Above query will return count 1 if table 'TABLE_NAME' is present in Database

Answer 6

Look in the schema, might event be able to use sys.objects and check for a type at the same time.....

Something like