if exists (select * from sysobjects
		where id = object_id('dbo.sp_deletedatabase') and sysstat & 0xf = 4)
	drop procedure dbo.sp_deletedatabase
GO

create procedure sp_deletedatabase;1
(
@databasename varchar(30)
)
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	26 Oct 1996						*/
/*	Description:	This SP can be used to drop a database & device files by*/
/*			a SQL Executive job.					*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
declare @retcode int,
	@mesg varchar(255),
	@exec_str varchar(255),
	@taskname varchar(100)
if user_id() <> 1
	begin
		raiserror ('Only DBO can perform this action.',-1,-1)
		return(1)
	end

-- check for conflicting database name.
if db_id(@databasename) is null
	begin
		select @mesg = 'Unable to locate database entry. Please give a valid name.'
		raiserror (@mesg,-1,-1)
		return(1)
	end

-- check the task also, if not present, then create.
select @taskname = 'Delete Db - ' + SYSTEM_USER
create table #task_info (name varchar(100), id int, subsystem varchar(30),
server varchar(30) null, username varchar(30) null, dbname varchar(30), enabled tinyint)
insert #task_info exec @retcode = msdb..sp_helptask @taskname
if @retcode <> 0
	return(1)
-- fire the task for dropping the database.
select @exec_str = 'sp_deletedatabase;2 ''' + @taskname + ''', ''' +
			@databasename + ''', ' + host_name()
if exists(select name from #task_info where name = @taskname)
	exec @retcode = msdb..sp_updatetask @taskname, @command = @exec_str
else
	exec @retcode = msdb..sp_addtask @taskname, 'TSQL', @username = 'dbo',
				@databasename = 'master', @command = @exec_str, @enabled = 0

if @retcode <> 0
	return(1)

exec @retcode = msdb..sp_runtask @taskname
if @retcode <> 0
	return(1)
select @mesg = 'Delete operation of database started. You will receive an acknowledgement '+
		'after the process.' + char(10) + 'Please wait and relax.'
raiserror(@mesg, -1, -1)
return(0)
GO

create procedure sp_deletedatabase;2
(
@taskname varchar(100),
@databasename varchar(30),
@machine varchar(30)
)
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	26 Oct 1996						*/
/*	Description:	This SP drops a database & device files and sends a NET	*/
/*			SEND message.						*/
/********************************************************************************/
declare @retcode int, @mesg varchar(255), @exec_str varchar(255)
exec msdb..sp_updatetask @taskname, @username = 'guest', @command = ''
exec @retcode = master..sp_dbremove_all @databasename
if @retcode <> 0
begin
	select	@mesg = 'Unable to drop the database. Contact System Administrator.',
		@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
	raiserror (@mesg,-1,-1)
	exec(@exec_str + '"' + @mesg + '"'', no_output')
	return(1)
end

select	@mesg = 'Database ' + @databasename +
		' & all associated devices has been successfully dropped.',
	@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
raiserror (@mesg,-1,-1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(0)
go