exec sp_configure 'allow updates', 1
reconfigure with override
go
if exists (select * from sysobjects
		where id = object_id('dbo.sp_dbremove_all') and sysstat & 0xf = 4)
	drop procedure dbo.sp_dbremove_all
GO

create procedure sp_dbremove_all
@dbname varchar(30) = null
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	18 Dec 1996						*/
/*	Description:	This SP can be used to drop a database & device files.  */
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
	declare @dbid int
	declare @dropdev varchar(10)
	declare @devname varchar(30)
	declare @pmsg varchar(255)
	declare @physname varchar(255)

	declare @cmd varchar(255)
	declare @cmd1 varchar(255)
	declare @cmd2 varchar(255)
	declare @cmd3 varchar(255)

	if @dbname is null
		begin
			raiserror(15131,-1,-1)
			return(1)
		end

	select @dropdev = 'dropdev'

	/* Check to see if database exists. */
	select @dbid = null
	select @dbid = dbid from sysdatabases where name=@dbname
	if @dbid is null
		begin
			raiserror(15010,-1,-1,@dbname)
			return(1)
		end

	/* Make sure no one is in the db. */
	if (select count(*) from sysprocesses where dbid = @dbid) > 0
		begin
			raiserror(15069,-1,-1)
			return (1)
		end

	if lower(@dropdev) = 'dropdev'
		begin
			print 'Dropping any devices used only by this database.'

			select @cmd1 = 'declare c1 cursor for
				select distinct d.name from sysdevices d,
					master.dbo.sysusages u
				where u.dbid = '+convert(varchar(5),@dbid)

			select @cmd2 = ' and u.segmap >= 0
 	  			and u.vstart between d.low and d.high
 	  			and d.status & 2 = 2	/* Physical devices only. */
 	  			and not exists
 	  				(select * from master.dbo.sysusages u2
 	  					where u2.dbid <> u.dbid'
 	  		select @cmd3 =			' and u2.vstart between d.low and d.high)'
			exec(@cmd1+@cmd2+@cmd3)

			open c1
			fetch c1 into @devname

			if @@fetch_status < 0
			begin
				print 'This database shares all of its devices with other databases.'
				print '-- no devices will be dropped.'
			end

			while @@fetch_status >= 0
				begin
					select @pmsg = 'Dropping device: '+@devname
					/*
					** Get the physical file name before
					** delete the row from sysdevices.
					*/
					select @physname = phyname
						from sysdevices
						where name = @devname

					/*
					** Release file handle for physical device.
					*/

					dbcc devcontrol(@devname,offline)
                                        if @@error = 0
                                        begin
			                        select @pmsg = 'File: '''+@physname+''' closed.'
               					print @pmsg
                                        end

					delete from sysdevices where name = @devname

					select @cmd = 'del '+@physname
					select @cmd = 'xp_cmdshell '''+@cmd+''''
					exec (@cmd)

					/* See if the delete was successful. */
					exec('dbcc devcontrol(''' +
						@physname +
						''',filesize) with no_infomsgs')
					if @@error <> 0 /* => file can't be opened. */
						begin
							print 'Couldn''t delete device file'
							return(1)
						end
					else
						print 'Physical file deleted.'

					fetch c1 into @devname
				end

			exec('deallocate c1')
		end

	update sysdatabases set status = 256 where dbid=@dbid
	dbcc dbrepair(@dbname,dropdb,noinit)
	print 'Database removed.'

	return(0)
GO


exec sp_configure 'allow updates', 0
reconfigure with override
go