14

I'm new in PL SQL, and I need to check if table exist on server and drop it.

Thanks in advance, Goran

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
Goran
  • 1,744
  • 3
  • 12
  • 12

5 Answers5

20

you can query the tablenames

select tname from tab where tname = 'TABLE_NAME_TO_SEARCH_FOR';
schoetbi
  • 12,009
  • 10
  • 54
  • 72
  • 4
    Note that the table name must be upper case even if you have created the table without uppercase. – Elmue Apr 23 '17 at 23:11
9
select tname from tab where tname = 'TABLE_NAME';
Kevin Crowell
  • 10,082
  • 4
  • 35
  • 51
7

This is where the true power of the information schema comes in. A simple query will point you in the right direction

SELECT
  *
FROM
  information_schema.tables
WHERE
  table_name='salesorders';

This can then be used in plpg function

CREATE OR REPLACE FUNCTION table_exists(v_table text)
  RETURNS boolean AS
$BODY$
  DECLARE
    v_count int;
    v_sql text;
BEGIN
  v_sql = 
    'SELECT ' ||
    '  count(1) ' ||
    'FROM ' ||
    '  information_schema.tables ' ||
    'WHERE ' ||
    E'  table_name=\'' || v_table || E'\'';

  EXECUTE v_sql INTO v_count;

  RETURN v_count>0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Use the function 

select * from table_exists('salesordesrs');

That should be enough to get you going.

OOPS Seems I misread the original posters question. I've answered for PostgreSQL.

Peter.

Peter Henderson
  • 127
  • 4
6

The most efficient method is, don't. Just drop the table. If the table didn't exist already, it'll raise an exception.

Running a query just before dropping the table is just wasting time doing what Oracle will do automatically for you.

You can handle the exception however you want, e.g.:

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "MYTABLE"';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
      DBMS_OUTPUT.put_line('the table did not exist!');
    ELSE
      RAISE;
    END IF;
END;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • @user428955: no, that won't work - a bind variable cannot be used for a table name. – Jeffrey Kemp Jan 24 '13 at 03:01
  • Are you sure? http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems017.htm seems to disagree with you. – KJP Apr 04 '13 at 15:31
  • yes, I am sure. There's nothing in that link that suggests that a *table name* can be supplied in a bind variable. – Jeffrey Kemp Apr 05 '13 at 05:13
  • Quoted from your link: *"You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement."* – Jeffrey Kemp Apr 05 '13 at 05:13
  • I would rather go for an if then for an exception. – florin May 28 '13 at 17:48
  • @florin: there's no need for an IF here. – Jeffrey Kemp May 29 '13 at 01:02
  • @JeffreyKemp thank you, you are correct I gave a bad example..what I was trying to avoid was a new person allowing a sql injection by just pipping the table name. It appears to the oracle doesn't want to allow anyone to bind because doing so is a bad idea really(because your taking a variable in for a table to remove). http://pastebin.com/4N5VJfcJ tested it, just to be sure..exception is thrown -SQL Error: ORA-00922: missing or invalid option – Bostwick Jul 21 '14 at 14:59
3

I had some troubles with the solutions above, as my DB has a peculiar tree structure. This should give every table in your schema:

SELECT
   table_name
FROM
   all_tables
WHERE
   table_name = '<your table here>'
Rubén CG
  • 41
  • 6
  • 1
    Note that the table name must be upper case even if you have created the table without uppercase. – Elmue Apr 23 '17 at 23:12