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  :    https://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

This page was last updated on May 01, 2006 04:28 PM.