Re: EXISTS
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 28 Jan 2009 10:29:15 -0800 (PST)
Message-ID: <f0f4c6bb-8a6d-4d5f-8da0-678d401c4ab4_at_u18g2000pro.googlegroups.com>
On Jan 28, 12:31 pm, Andreas Mosmann <mosm..._at_expires-31-01-2009.news- group.org> wrote:
> Michel Cadot schrieb am 28.01.2009 in
> <49808224$0$24227$426a7..._at_news.free.fr>:
>
> > "Wilfrid" <grill..._at_yahoo.com> a écrit dans le message de news:
> > 49807732$0$10060$426a7..._at_news.free.fr...
> >| IF OBJECT_ID('tablename') is null
> >| CREATE TABLE tablename
> >| (
> >| id int not null,
> >| name nvarchar(10) null
> >| )
> >| go
> >|
> >| Thanks in advance for your help
> >|
> >|
> > Check if it is in user_tables
>
> 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
>
> > Regards
> > Michel
>
> HTH
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
Date: Wed, 28 Jan 2009 10:29:15 -0800 (PST)
Message-ID: <f0f4c6bb-8a6d-4d5f-8da0-678d401c4ab4_at_u18g2000pro.googlegroups.com>
On Jan 28, 12:31 pm, Andreas Mosmann <mosm..._at_expires-31-01-2009.news- group.org> wrote:
> Michel Cadot schrieb am 28.01.2009 in
> <49808224$0$24227$426a7..._at_news.free.fr>:
>
> > "Wilfrid" <grill..._at_yahoo.com> a écrit dans le message de news:
> > 49807732$0$10060$426a7..._at_news.free.fr...
> >| IF OBJECT_ID('tablename') is null
> >| CREATE TABLE tablename
> >| (
> >| id int not null,
> >| name nvarchar(10) null
> >| )
> >| go
> >|
> >| Thanks in advance for your help
> >|
> >|
> > Check if it is in user_tables
>
> 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
>
> > Regards
> > Michel
>
> HTH
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
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 -- Received on Wed Jan 28 2009 - 12:29:15 CST