PDA

View Full Version : problem in SQL script to drop tables

siddharth
01-14-02, 03:50
Hi,

How can we write a SQL statement to drop a table. The drop statement should not generate any error if the table doesn't exists. I have achieved the same in Oracle by catching the 'table not found exception'

The code I wrote is as follows:

CREATE PROCEDURE drop_table(pTableName VARCHAR2)
IS
vCommand VARCHAR2(1024);
table_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_not_exist,-942);
BEGIN
vCommand := 'DROP TABLE ' || pTableName || ' CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE vCommand;
EXCEPTION
WHEN table_not_exist THEN
NULL;
WHEN OTHERS THEN
RAISE;
END drop_table;

Can I do something equivalent in Sybase?
Thanks,
Siddharth
MattR
01-14-02, 09:24
Sybase doesn't require you to jump through hoops and "catch" the error code. Seems kludgy. In T-SQL, you can do something like this:


if exists( SELECT *
FROM sysobjects
WHERE ID = object_id( 'table_name' ) )
DROP TABLE table_name


I think the object_id is indexed in sysobjects whereas the name is not, although I may be wrong (runs off to check the sp_help sysobjects output :)).

Ok, I see there's (at least in 12.5) an index upon id and a composite index upon name, uid. So either query will work fine.

edit: I see you found the answer in MS SQL Forums as well.
siddharth
01-15-02, 04:02
Hi,
Sorry to be posting this on this forum, but I would just like to know if such a statement is possible in Oracle and DB2 also?

i.e. statement of the type

if exists( SELECT *
FROM sysobjects
WHERE ID = object_id( 'table_name' ) )
DROP TABLE table_name

Thanks and regards,
Siddharth.