Check if table exists in database - PL SQL

Question

I am new to PL SQL, and I need to check if a table exists on the server and leave it.

Thanks in advance, Goran

Answer 1

you can request tablenames tags

select tname from tab where tname = 'TABLE_NAME_TO_SEARCH_FOR'; 

Answer 2

 select tname from tab where tname = 'TABLE_NAME'; 

Answer 3

Here comes the true power of the information scheme. A simple request will point you in the right direction

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

This can then be used in the 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 function

 select * from table_exists('salesordesrs'); 

That should be enough for you to go.

OOPS It seems that I am not reading the question correctly with the original posters. I was responsible for PostgreSQL.

Peter.

Answer 4

The most effective method is not. Just lower the table. If the table no longer exists, it will throw an exception.

Running a query before dropping a table simply wastes time on what Oracle will do for you automatically.

You can handle the exception, but want, for example:

 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; 

Answer 5

I had some problems with the solutions above, since my database has a kind of tree structure. This should contain every table in your schema:

 SELECT table_name FROM all_tables WHERE table_name = '<your table here>'