Often bugs can be masked if you do not start with a perfectly pristine environment to work in. Getting a clean environment however can be sometimes quite onerous; for example, uninstalling and reinstalling a database. While you must do that a couple times during the testing (and development) cycle, that isn’t always the most efficient use of time. One trick for cleaning a database is to install a stored procedure on the database that drops the tables/triggers/indexes/procedures your software uses.

Here is a procedure I use to to drop the tables one of my products creates. It is in Transact-SQL so would need some tweaking to convert to PL/SQL for oracle, but there is nothing specific to SQL server I don’t think.


/* set our database -- change adam to whatever your db is called*/
use adam
go

/* delete our procedure if it exists already */
if exists(select name from dbo.sysobjects where name = 'drop_adam_tables' and type = 'P')
 	begin
 		drop procedure dbo.drop_adam_tables
 	end
go

/* recreate it */
create procedure dbo.drop_adam_tables
 	@prefix varchar(5)
as
 	declare @tbl varchar(40)
 	declare @combined varchar(40)

  	/* suppress the output of the inserts and drops */
 	set nocount on

  	/* build a temp table which has our table names */
	create table #adam_tables (
 		t_name varchar(40)
 	)
 	insert #adam_tables (t_name) values('changed_table_name_1')
 	insert #adam_tables (t_name) values('changed_table_name_n')

  	/* systematically remove all our tables */
 	declare tbl_cursor cursor for
 		select * from #adam_tables
 	open tbl_cursor

  	/* fetch first row */
	fetch next from tbl_cursor into @tbl

  	/* loop and remove */
 	while @@fetch_status = 0
 		begin
 			set @combined = @prefix + '_' + @tbl
 			if exists(select name from dbo.sysobjects where name = @combined and type = 'U')
 				begin
 					execute('drop table ' + @combined)
 				end
 			fetch next from tbl_cursor into @tbl
 		end

  	/* cleanup */
 	close tbl_cursor
 	deallocate tbl_cursor
 go

As you can see, it only does tables (because that’s what I needed…), but could be expanded to other database objects by adding another column to the temporary table then building the execute command appropriately. The

set @combined = @prefix + '_' + @tbl

is because our product prepends a user specified prefix to each table it creates. To run the procedure just requires this command

execute drop_adam_tables your_prefix

via eclipse or python or anything else to have your database at a clean state.