Hi all,

I want to do something like this in DB2.

MS-SQL:
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'TEST')
DROP TABLE dbo.TEST;

Thanks,
Newbie

Hi there,

You can do the following

IF EXISTS (SELECT * FROM sysibm.sysobjects WHERE name = 'TEST')
then
DROP TABLE dbo.TEST;
end if ;


ciao

Falcon_daown

Unfortunately it doesn't work. I try to execute a string in which to drop the table but I notice that in a string you cannot drop a table.
And the table is named sysibm.systables not sysobjects (this is the sql server system table)

Thank you AStefan, I had been tied up with other issues and i had not tried getting this to work. Actually the situation i am in is that it would be nice if i could drop stored procedures (if existing) before i want to create them.

I tried:

IF EXISTS (SELECT name FROM syscat.procedures WHERE procname = 'sp_test') THEN
DROP Procedure sp_test;
END IF;

Others,
Any pointers? Thanks in Advance.

Newbie

I have a similar problem but in Db2 it is difficult to drop if exists an object (table, view or whatever). This needs appear generally in a script.
My idea was to create one-two procedures who will help me to generate a script with drop procedures.
I still working.
The first procedure is DROP_IF_EXISTS. This one inserts in the table OBJECT_NOT_CREATED the existing procedure (defined by name and schema).
The second procedure is GENERATE_DROP_SCRIPT. It takes from the table OBJECT_NOT_CREATED and fill an output string with the contains of the script (drop procedure 1 @ drop procedure 2 etc.). I hope will helps, I didn't find another way.
The procedures are the followings:

CREATE PROCEDURE DROP_IF_EXISTS (IN V_OBJECT_NAME VARCHAR(128),IN V_SCHEMA_NAME VARCHAR(128))
SPECIFIC DROP_IF_EXISTS
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
LANGUAGE SQL
P1: BEGIN
DECLARE V_COUNT INTEGER DEFAULT 0;

SELECT COUNT (*)
INTO V_COUNT
FROM SYSCAT.PROCEDURES
WHERE PROCSCHEMA = V_SCHEMA_NAME AND PROCNAME = V_OBJECT_NAME;

IF V_COUNT = 1
THEN
IF NOT EXISTS (SELECT * FROM OBJECT_NOT_CREATED WHERE NAME = V_OBJECT_NAME AND SCHEMA = V_SCHEMA_NAME)
THEN INSERT INTO OBJECT_NOT_CREATED (NAME, SCHEMA, DATE, TYPE, DROP_CONDITION)
VALUES (V_OBJECT_NAME, V_SCHEMA_NAME, CURRENT DATE, 'PROCEDURE', 'DROP PROCEDURE ' || V_SCHEMA_NAME || '.' || V_OBJECT_NAME);
END IF;
END IF;
END

CREATE PROCEDURE GENERATE_DROP_SCRIPT (OUT DROP_OBJECTS_LIST VARCHAR(2000))
SPECIFIC GENERATE_DROP_SQL
DYNAMIC RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL
P1: BEGIN

DECLARE V_ERROR INT DEFAULT 0;
DECLARE V_DROP_CONDITION VARCHAR(130);

DECLARE C_DROP_IF_EXISTS CURSOR WITH RETURN FOR
SELECT DROP_CONDITION FROM OBJECT_NOT_CREATED;

DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND
SET V_ERROR = 1;

SET DROP_OBJECTS_LIST = '';
OPEN C_DROP_IF_EXISTS;

FETCH C_DROP_IF_EXISTS INTO V_DROP_CONDITION;
WHILE V_ERROR = 0 DO

SET DROP_OBJECTS_LIST = DROP_OBJECTS_LIST || V_DROP_CONDITION || ' @ ';
FETCH C_DROP_IF_EXISTS INTO V_DROP_CONDITION;
END WHILE;

CLOSE C_DROP_IF_EXISTS;
END

If Exists(select Name From Sysibm.systables Where Name = 'MyTab' And Creator = 'MySchemaOfTable') Then
Drop Table MySchemaOfTable.MyTab;
End If;

Unfortunately it doesn't work. Thanks anyway.