Check if table exists in the database - PL SQL

I'm new in PL SQL, and I need to che开发者_StackOverflowck if table exist on server and drop it.

Thanks in advance, Goran

you can query the tablenames

select tname from tab where tname = 'TABLE_NAME_TO_SEARCH_FOR';

select tname from tab where tname = 'TABLE_NAME';

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.

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;

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