Kill Databases on SQL Server

Hai,

I wrote a stored procedure for drop all databases from the SQL Server except sql server system databases.

The logic behind the stored procedure that when we execute this stored procedure , firstly a cursor open with all the database name from master db except system databases name. And each cursor loop ,it kill all the process running in sql server that related to corresponding database. So we can easily drop that database from the server.

I just wrote this procedure for an academic interest. Don't try to use in a bad way.


Create Procedure spKillDB

With Encryption
As
Begin
    Set Nocount On;

    Declare @DBName        varchar(50)
    Declare @spidstr    varchar(8000)

    Set @spidstr = ''

    Declare cur Cursor Fast_Forward Read_Only For
        Select name From Master..sysdatabases
        where dbid not in (1,2,3,4)
        Order By dbid Desc
    Open cur
    Fetch Next From cur Into @DBName
    While @@fetch_status <> -1
    Begin
        Select @spidstr=coalesce(@spidstr,',' )+'kill '+Convert(varchar(10), spid)+ '; '
        From Master..sysprocesses Where dbid=db_id(@dbname)

        If Len(@spidstr) > 0
        Begin
            Exec(@spidstr)
        End   
       
        Set @spidstr = 'DROP DATABASE ' + @dbname
        Exec(@spidstr)       
       
    Fetch Next From cur Into @DBName
    End
    Close cur
    Deallocate cur
End