How do I use CREATE OR REPLACE?

This works on functions, procedures, packages, types, synonyms, trigger and views.

Update:

After updating the post for the third time, I'll reformulate this:

This does not work on tables :)

And yes, there is documentation on this syntax, and there are no REPLACE option for CREATE TABLE.

One of the nice things about the syntax is that you can be sure that a CREATE OR REPLACE will never cause you to lose data (the most you will lose is code, which hopefully you'll have stored in source control somewhere).

The equivalent syntax for tables is ALTER, which means you have to explicitly enumerate the exact changes that are required.

EDIT: By the way, if you need to do a DROP + CREATE in a script, and you don't care for the spurious "object does not exist" errors (when the DROP doesn't find the table), you can do this:

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE owner.mytable';
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode != -0942 THEN RAISE; END IF;
END;
/

There is no create or replace table in Oracle.

You must:

DROP TABLE foo;
CREATE TABLE foo (....);

CREATE OR REPLACE can only be used on functions, procedures, types, views, or packages - it will not work on tables.

Following script should do the trick on Oracle:

BEGIN
  EXECUTE IMMEDIATE 'drop TABLE tablename';
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode != -0942 THEN RAISE; 
    END IF;
END;

Does not work with Tables, only functions etc.

Here is a site with some examples.

I would do something like this

  begin
     for i in (select table_name from user_tables where table_name = 'FOO') loop
        execute immediate 'drop table '||i.table_name;
     end loop;
  end;

  execute immediate 'CREATE TABLE FOO (id NUMBER,
                                       title VARCHAR2(4000)) ';

If this is for MS SQL.. The following code will always run no matter what if the table exist already or not.

if object_id('mytablename') is not null //has the table been created already in the db
Begin
     drop table mytablename
End

Create table mytablename (...