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

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