From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: How Do You Empty a database?????????
Organization: 
Date: Fri, 11 Sep 1992 22:11:57 GMT

In article <davidw.716184967@manning>, davidw@cs.UAlberta.CA (David R! 
Woloschuk) writes:
> 
> Does anyone know if there is a command that a User could issue
> that *empties* a database (tables, forms, applications etc.) 
> without actually destroying the database?
> 
> I want to find a command that, in essence, returns the database to
> a state that it was in *just* after it was created using
> 'createdb'.  
> 
> 'destroydb' followed by 'createdb' cannot be used in this case.

There's no single command.  But it's easy to query the system catalogs
and generate a script to do it.  I've attached a first stab, which is a
VMS .com file.  

It drops all non-system tables, views, and procedures.  Dropping the
tables automatically causes indexes to go, and some views.  I wrote the
script so that it drops _all_ views, since it's possible that there are
non-INGRES (user-defined) views defined on the system catalogs.  You may
get error messages from these extraneous drops, but they can be ignored.

Lastly, it drops all the rows from ii_objects, which is the primary file
for front-end objects (forms, reports, QBFnames, ABF applications,
Vision, etc.).  Then there follows a sysmod, which should clear out the
rest of the front-end system catalogs.

Warning: this program has not been tested.  I didn't have a full
database handy that I wanted to flush.  8^)

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.
================================================================================
$! emptydb.com -- empty a database
$! by Mark Jaeger, University of Chicago
$!
$! Check if user needs help.
$!
$ p1 = f$edit(p1,"trim,lowercase")
$ if p1 .eqs. "" .or. p1 .eqs. "?" .or. p1 .eqs. "help" then $ goto show_usage
$!
$! Get arguments from command line.
$!
$ dbname = f$edit(p1,"trim,lowercase")
$!
$! Set up some constants that we need here.
$!
$ dbug = "false"
$ pid = f$getjpi(0,"pid")
$ proc = f$environment("procedure")
$ procname = f$parse(proc,,,"name")
$ tempfile = "sys$scratch:temp-''procname'-''pid'.com"
$!
$! Set up error handling.
$!
$ on error then $ goto fin
$ on control_y then $ goto fin
$!
$! Body.
$!
$ write sys$output "Generating script to empty the database..."
$ report 'dbname' -isys$input: -f'tempfile'
$ deck
.name emptydb
.declare otype = varchar(80) not null
.query select distinct object_owner = table_owner
	, object_name = table_name
	, object_type = table_type
	, dba = dbmsinfo('dba')
	from iitables
	where table_owner != '\$ingres' and table_type != 'I'
	and system_use != 'S'
	union select distinct object_owner = procedure_owner
	, object_name = procedure_name
	, object_type = 'P'
	, dba = dbmsinfo('dba')
	from iiprocedures
	where procedure_owner != '\$ingres'

.sort object_owner : a
	, object_type : a

.header report
	.noff
	.pl 1
	.fmt dba(c0)
	.fmt object_owner(c0)
	.fmt object_name(c0)

.header object_owner
	.pln '$ sql -u',object_owner," 'dbname'"
	.pln 'commit ; set autocommit on \g'

.detail
	.if     object_type = 'T' .then .let otype := 'table'
	.elseif object_type = 'V' .then .let otype := 'view'
	.elseif object_type = 'P' .then .let otype := 'procedure'
	.else				.let otype := 'thing'
	.endif
	.pln 'drop ',otype,' ',object_name,'\g'

.footer report
	.pln '$ sql -u',dba," 'dbname'"
	.pln 'delete from ii_objects \g'
	.pln "$ sysmod -s 'dbname'"
	.pln '$ exit'

$ eod
$!
$! Invoke the script that just got created.
$! Here's where the stuff actually gets dropped.
$!
$ @'tempfile'
$!
$! Clean up.
$!
$fin:
$ on error then $ continue
$ on control_y then $ continue
$ delete /nolog 'tempfile';*
$!
$ exit ! emptydb.com

$show_usage:
$ type /page sys$input
$ deck

	Usage: @emptydb <dbname>

Cautions: You must be the dba or an INGRES superuser to run this program.
This program won't work using INGRES/Net, but only because of the
sysmod.

$ eod
$ exit ! emptydb