# Databases > Oracle How to drop all tables from a user

## akmohanty

Do help anybody about the query how to drop all tables from a user without droping the user.

----------


## Innila

Hi akmohanty,

Connect to the user of which the tables have to be dropped.
Using the following select query, the script for dropping all the tables can be prepared.
*SQL>  select 'drop table ' || tname || ' ;' from tab ;*

Spool the output in a file & the file has to be executed, so that all the tables of the user will be dropped.
U can use "*set heading off*" so that the headings display will be eliminated.

Hope it helps u.

----------


## susarlasireesha

create or replace PROCEDURE drop_many_tables
(tablename IN VARCHAR2)
IS
  cur       integer;
begin
  cur:= dbms_sql.OPEN_CURSOR();
    for t in (select object_name from user_objects where object_type='TABLE' and object_name like tablename) loop
    execute immediate
    'drop table ' ||t.object_name|| ' cascade constraints';
    end loop;
  dbms_sql.close_cursor(cur);
end drop_many_tables;
Try this ...............

----------

