开发者

Using SQL query to determine if a table exists

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.


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜