Using SQL query to determine if a table exists
Guys is there any other way to determine a table exists other than below
select count(*) from <开发者_运维问答table> where rownum =1
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.
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';
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'
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-'
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.
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
Look in the schema, might event be able to use sys.objects and check for a type at the same time.....
Something like
精彩评论