Hello,

There has been many communication on the exists command on newsgroups, web
sites... but I could not find anything close to this (below) that it can be
possible with MS SQL.

I am migrating databases and scripts from SQL to Oracle and I am trying to
make things as simple as possible.
Is there a simple way with oracle that does the same thing as the script
below in SQL?
I am testing if the table 'tablename' exists in the schema and if no create
it.

IF OBJECT_ID('tablename') is null
CREATE TABLE tablename
(
id int not null,
name nvarchar(10) null
)
go

Thanks in advance for your help

"Wilfrid" <grille11 (AT) yahoo (DOT) com> a écrit dans le message de news: 49807732$0$10060$426a74cc (AT) news (DOT) free.fr...
Check if it is in user_tables

Regards
Michel

Michel Cadot schrieb am 28.01.2009 in
<49808224$0$24227$426a74cc (AT) news (DOT) free.fr>:


that means something like
select
count(*) COUNT_OF_TABLES
from
user_tables UT
--ALL_TABLES A
where
UT.TABLE_NAME=&MyTable
--A.TABLE_NAME=&MyTable
--and
--A.OWNER=&MySchema

COUNT_OF_TABLES is 0 or 1

I do not know whether you want to do it manually, by any script language
or if you want to write a GUI for that, but if you use one of that
queries you will find out what you want.

also try out
SELECT * FROM DICTIONARY WHERE upper(COMMENTS) like '%TABLE%'
to find out more

Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

On Jan 28, 12:31*pm, Andreas Mosmann <mosm... (AT) expires-31-01-2009 (DOT) news-
group.org> wrote:
Using PL/SQL you can easily write a does_table_exist user function and
test it via an IF statement and use an execute immediate statement to
run the DDL. However, why bother?

Just execute the drop followed by the create. An error in the drop
will not terminate the SQLPlus script and the create will be executed.

To eliminate error message do all the drops in one script and the
creates in another.

Oracle provides a free data migration tool that can convert T-SQL to
Oracle SQL and PL/SQL. It is available at http://otn.oracle.com.

HTH -- Mark D Powell --

On Jan 28, 7:18*am, "Wilfrid" <grill... (AT) yahoo (DOT) com> wrote:
An additional warning, only because I've seen it often from people
coming from MS: creating tables is something you don't normally want
to do at runtime. It's an expensive operation, and DDL does commits
before and after. If you need work tables, see the concept of global
temporary tables. A fellow named Tom Kyte has written some excellent
books describing these and other implementation differences you need
to know about when switching database engines.

As far as installation or migration scripts and such, it's a
reasonable thing to do as Mark described, though I've never been a fan
of blindly dropping tables.

SQL is a language, not something owned by Microsoft. EXISTS happens
to be a language statement in SQL, so your thread heading is a bit
misleading, as you found when you searched for it. Just so you know,
it helps to have the heading reflect the thread.

jg
--
@home.com is bogus.
http://comics.com/pearls_before_swine/2009-01-25/