create procedure sp_dbremove2
@dbname varchar(30) = null,
@dropdev varchar(10) = null
as

declare @dbid int
declare @devname varchar(30)
declare @pmsg varchar(255)
declare @physname varchar(255)

declare @cmd1 varchar(255)
declare @cmd2 varchar(255)
declare @cmd3 varchar(255)
/*******************************************************************
                                                                  
Removes database and the devices that the database exclusevly uses.

Use at own risk  

sp_dbremove2 <dbname>, 'dropdev'
Copyright (c) Tibor Karaszi                                      
                                                                  
*******************************************************************/

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

if lower(@dropdev) <> 'dropdev' and @dropdev is not null
begin
raiserror(15131,-1,-1)
return(1)
end

/* 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 INSENSITIVE 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

-- Drop the database here, to be able to remove devices later
EXEC('DROP DATABASE ' + @dbname)
print 'Database removed.'
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.
*/

EXEC sp_dropdevice @devname,
'delfile'
                                                if @@error = 0
                                                        begin
                        select
@pmsg = 'File:  '''+@physname+''' closed.'
                        print
@pmsg
                                                        end

fetch c1 into @devname
end

exec('deallocate c1')
end


return(0)