Current Position:Home > To drop all the tables in a database

To drop all the tables in a database

Update:10-11Source: network consolidation
Advertisement
hi,
I have 25000 tables in a database.I want ot delete all the tables and then i have to import the dump.For deleting all the tables what query has to be given.
Thanks in advance,
R.Ratheesh

The Best Answer

Advertisement
The code would be
BEGIN
   FOR c IN (SELECT owner,
                    table_name
               FROM all_tables
              WHERE owner IN ('YOUR_OWNER1', 'YOUR_OWNER2_ETC'))
   LOOP
      EXECUTE IMMEDIATE    'drop table '
                        || c.owner
                        || '.'
                        || c.table_name
                        || ' cascade constraints';
   END LOOP;
END;
the user ,tablespaces ,datafiles has to be recreatedbut still dropping the user would be the simplest option
you don't have to recreate tablespaces and datafiles for that.
  • To drop all the tables in a database Update:10-11

    hi, I have 25000 tables in a database.I want ot delete all the tables and then i have to import the dump.For deleting all the tables what query has to be given. Thanks in advance, R.RatheeshThe code would be BEGIN    FOR c IN (SELECT owner,          

  • How to delete/drop all the tables from SQL Server Database without using Enterprise Manager? Update:11-30

    How to delete/drop all the tables from SQL Server Database without using Enterprise Manager? I tried using DROP Tables, Truncate Database, Delete and many more but it is not working.  I want to delete all tables using Query Analyzer, i.e. through SQL

  • Is there a way to create "temporary" tables in SAP database? Update:10-11

    Hello, Is there a way to create temporary tables in ABAP? Here is our scenario: 1. Invoke a custom RFC that creates a temporary table and returns the name of the table. 2. Invoke another custom RFC and pass this table name as parameter. This RFC inte

  • Unable to drop SQL azure table Update:10-11

    Hi, I'm trying to drop SQL azure table. However I can delete data inside the table. When using drop command, it is taken long time processing and finally this error message 'Connection Failed'. Please help me. ThanksThat sounds like an internal error

  • Drop and recreate table in stored procedure Update:10-11

    Hi all When creating tables using Transact-SQL scripts, I have always preferred to drop the table if it exists and then create it explicitly using CREATE TABLE.  For two reasons: 1) It does not matter if it is the first time the SP is run ie. if I cr

  • Creating and Dropping a temporary table in Bi publisher? Update:10-11

    Hi all. My database is sybase. I created a new data set in BI publisher where it creates a temporary table, uses that temp table in a select query and then I dropped the temporary table. I wrote all these 3 SQL statements in a single dataset. This is

  • Table changes in database are not captured in ODI model level Update:10-11

    Hi All, Can any one help me how to fix the bug in ODI. Table changes in database are not captured in ODI model level. Thanks in advanceI created the interface which is running successfully. Now i did some changes in target table(data base level). I r

  • Cannot drop column from table owned by SYS Update:11-30

    Hello everybody, How can I drop a column of table which is owned by sys? thanks! Result as follows: SQL> ALTER TABLE HELLO_TB DROP COLUMN NUM; ALTER TABLE HELLO_TB DROP COLUMN NUM ERROR at line 1: ORA-12988: cannot drop column from table owned by SYS

  • How do I run sp_spaceused for all tables in a database? Update:11-30

    Hi, I am struggling to understand how to run the sp_spaceused sproc for all tables in a database. I know how to use it for one table but how would I replicate it automatically for each table in a given database? Regards, Ian.I just managed to alter t

  • Way to programmatically drop-and-create-tables? Update:11-30

    Hi all, I have a persistence unit which exists to provide lookup information to my application. It is populated by data that is available at runtime. It takes a long time to parse this data, and therefore my intention is to only parse it once at the