5

Possible Duplicate:
SQL Server: Check if table exists
Oracle: If Table Exists

I'm trying to create a table and insert some values, but before I do I must make sure that the table doesn't already exist. How do you check for this?

Community
  • 1
  • 1
Programmer
  • 133
  • 1
  • 5
  • 1
    which language , as in mysql you can use if exists – Satya Apr 19 '12 at 04:43
  • I'm currently using Oracle right now. It seems that all the answers assumed I was using SQL Server since I forgot to mention it in there.... – Programmer Apr 19 '12 at 05:02
  • 1
    Why do you want to do this? With Oracle there is rarely a need to create tables on the fly (unlike certain other flavours of DBMS). – APC Apr 19 '12 at 06:20

5 Answers5

1

You can try simply SELECTing from it and capturing the error, otherwise you will need to write DB specific SQL to query their respective metadata tables and look there.

Will Hartung
  • 115,893
  • 19
  • 128
  • 203
0

IF MySQL:

select count(*) from my_tables where table_name='table_1'; 
If count>0 then  ...
Erre Efe
  • 15,387
  • 10
  • 45
  • 77
0

Assuming SQL Server...Query sysobjects:

select * from sysobjects where xtype='U' and name ='tablename'
JP.
  • 5,536
  • 7
  • 58
  • 100
  • Or for SQL Server **2005** and newer - even better - query `sys.tables`: `if exists (select * from sys.tables where Name = 'tablename') .....` – marc_s Apr 19 '12 at 04:56
0

In MySQL, you can use the CREATE TABLE IF NOT EXISTS construct and run it before your INSERT query. This will create the table if it doesn't exist and will do nothing if the table is there.

CREATE TABLE IF NOT EXISTS myTable (
    ....
)

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/create-table.html

David Z.
  • 5,621
  • 2
  • 20
  • 13
0

This simple query gives you details about the particular table which are created by users in oracle. Try it.

 select * from user_tables where table_name = 'tablename';
hitman047
  • 1
  • 1