Re: dropping a large number of tables?

On Wed, 21 Oct 1998 00:06:38 GMT, bigdavemaw_at_my-dejanews.com wrote:

>I have a large number of tables that are all start with "mytable_".
>i.e. mytable_1, mytable_2, ...
>
>What would be the best way to drop all tables in my table space that start
>with mytable_ ?

You can do something like this, assuming that you are using SQL*Plus:

	spool c:\XXX.sql
	select 'drop table ' || table_name || ';'
	from user_tables
	where table_name like 'mytable_%';
	spool off

Review the contents of the file here, and then execute it with the following command:

        @c:\xxx.sql

Be sure to look at the file at least once before executing it, just to be sure that you don't drop the wrong tables.

>
>I know there must be a system table that lists all tables for a tablespace,
>but don't know what it is.

	user_tables	- tells you about tables
	user_views		- about views
	user_tab_cols (i think) tells about columns

Try issueing the following:

	select view_name
	from user_views
	where view_name like 'USER%';

you should get a long list of data dictionary views that you can query for different things.

Jonathan Received on Wed Oct 21 1998 - 00:00:00 CDT