DROP TABLE, VIEW, ... only IF EXISTS

Gregor KovaÄ

Hi!

Is it possible do to something like this:
DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Jul 6 '06 #1

Subscribe Post Reply

45801

Konstantin Andreev

"Gregor Kovac" <gr**********@mikropis.siwrote: news:6y********************@news.siol.net...

Is it possible do to something like this: DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?

Long and cumbersome way. Just drop and ignore errors. But, if you insist...
For table:
--------------
Begin atomic
if( exists(
select 1 from syscat.tables where tabschema = 'MYSCHEMA' and tabname = 'MYTABLE'
)) then
drop table MYSCHEMA.MYTABLE;
end if;
End

--------------
For index: syscat.table -syscat.indexes + 2 extra conditions: indschema & indname.
.... and so on...

Cheers,
--
Konstantin Andreev.

Jul 6 '06 #2

Paul Peters

"Gregor Kovac" <gr**********@mikropis.siwrote in message
news:6y********************@news.siol.net...

Hi!

Is it possible do to something like this:
DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Use the db2perf_quiet_drop procedure. The source code is available on

ftp://ftp.software.ibm.com/ps/produc...perf-1.0.1.zip

In the file db2perf_utils.db2 there is the procedure db2perf_quiet_drop
which suppresses the 'not found' message.

The call :

CALL db2perf_quiet_drop( 'procedure db2perf_crmsg' )@

drops the procedure db2perf_crmsg if this procedure exists.

Kind regards,
Paul

Jul 6 '06 #3

Gregor KovaÄ

Konstantin Andreev wrote:

Begin atomic
if( exists(
select 1 from syscat.tables where tabschema = 'MYSCHEMA' and tabname =
'MYTABLE' )) then
drop table MYSCHEMA.MYTABLE;
end if;
End

Hmm.. but it does not work for me.
I've saved this into a file TEST.SQL:
Begin atomic
Â* Â* if (exists(select 1 from syscat.tables where tabschema = 'MYSCHEMA' and
tabname = 'MYTABLE')) then
Â* Â* Â* Â* drop table MYSCHEMA.MYTABLE;
Â* Â* end if;
End
#

ran it like:
db2 -td# -f TEST.SQL

and got:
B21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "Â* Â*" was found following "Begin atomic ".
Expected tokens may include: "<space>". LINE NUMBER=2. SQLSTATE=42601

I've tried this approach, but to no avail.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Jul 6 '06 #4

Gregor KovaÄ

Paul Peters wrote:

>
Use the db2perf_quiet_drop procedure. The source code is available on

ftp://ftp.software.ibm.com/ps/produc...perf-1.0.1.zip

In the file db2perf_utils.db2 there is the procedure db2perf_quiet_drop
which suppresses the 'not found' message.

The call :

CALL db2perf_quiet_drop( 'procedure db2perf_crmsg' )@

drops the procedure db2perf_crmsg if this procedure exists.

Kind regards,
Paul

Cool, I like it :)) thanks.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Jul 6 '06 #5

Similar topics

HELP - can not drop table

by: Snorkerz | last post by:

Can anyone help, I'm trying to change some code on an asp page, this line: mySQL="drop table snorkTable" does not seem to close the table. Net time the page is refreshed I'm told that Table...

MySQL Database

##temp table already exists problem

by: nigel | last post by:

Hello I am using a temp table called ##temp in an SProc but often get the message that the table already exists. Could this be because the SProc is being run by more than 2 webpages at the same...

Microsoft SQL Server

All SQL's on a table hang, even a "drop table" statement.

by: Alanoly J. Andrews | last post by:

Hi, I have two tables (that I know of) in a database that seem to be corrupted. All SQL statements on the tables hang, "select * from..", "select count (*) from", "delete from..". I can't even...

DB2 Database

drop table conditionally

by: Yaro | last post by:

Hello Could anyone tell how can I drop table conditionally (UDB 8.2 Win) ? I have a script file create table_1@ create table_2@ etc.... I need add to this script statement like that

DB2 Database

Q: Drop table and check if table exists

by: Geoff Jones | last post by:

Hi I'm trying to drop a table by using: Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection) cmd.ExecuteNonQuery() but I get a syntax error: "Syntax error in DROP TABLE...

Visual Basic .NET

Table already exists exception

by: Irfan | last post by:

hi, All I have a table in Access and I am using a storedProcedure (Make-Table query) to overwrite this table. It gives an error that 'Table Already Exists', which is true. But i want it to...

Visual Basic .NET

.Net/SQL Drop Table Problem

by: The VanDerbeck Group | last post by:

The code below generates the indicated error. As a newbie to vb and SQL I have no idea how to track this down. I did verify that the connection state is open. My suspicion is that I have to connect...

Visual Basic .NET

How can I do conditional 'drop table' in Postgres

by: Dragan Matic | last post by:

if exists (select * from sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO For instance, this is a valid script in Ms SQL, it will drop table...

PostgreSQL Database

Drag & Drop Table Columns (new version, explained)

by: Romulo NF | last post by:

Greetings, I´m back here to show the new version of the drag & drop table columns (original script ). I´ve found some issues with the old script, specially when trying to use 2 tables with...

Javascript

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server