Check if table exists in the database - PL SQL

11
votes

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

Thanks in advance, Goran

5
oracleplsql

5 Answers

19
votes

you can query the tablenames

select tname from tab where tname = 'TABLE_NAME_TO_SEARCH_FOR';
9
votes
select tname from tab where tname = 'TABLE_NAME';
6
votes

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;
5
votes

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.

1
votes

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>'