Drop all Connections from a Database

,

On development servers, at times there is a need for dropping and renaming database(s). These scripts can be used for achieving these tasks. The usp_DropDB script will kill the connections to the database and then drop the database. It takes care of the space in the database name, if any and also makes sure that the database exists on the server.

/*This proc will be called by the usp_ForceDropDB*/

CREATE PROCEDURE usp_DropDB
    @DatabaseName varchar(50)
WITH ENCRYPTION
AS
BEGIN
    if (select count(*) from master.dbo.sysprocesses where dbid = 
(select dbid from master.dbo.sysdatabases where name=@DatabaseName)) <> 0
    BEGIN
        EXEC SetMessages
        RAISERROR(60009,16,1)
        RETURN
    END
    SET @DatabaseName = QUOTENAME(@DatabaseName)
    EXEC ('DROP DATABASE ' + @DatabaseName )
END
GO

/*Drops a database even if there are connections by killing all the connections.*/

CREATE PROC usp_ForceDropDB
    @DatabaseName varchar(50)
WITH ENCRYPTION
AS
BEGIN
    --Cursor for all the spids running against this database
    DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
        SELECT spid
        FROM master.dbo.sysprocesses
        WHERE dbid =
         (SELECT dbid FROM master.dbo.sysdatabases
          WHERE name = @DatabaseName)
    DECLARE @SysProcId smallint
    OPEN SysProc    --kill all the processes running against the database
    FETCH NEXT FROM SysProc INTO @SysProcId
    DECLARE @KillStatement char(30)
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
        EXEC (@KillStatement)
        FETCH NEXT FROM SysProc INTO @SysProcId
    END
        WAITFOR DELAY '000:00:01'
        EXEC usp_DropDB @DatabaseName
END
GO

/*This proc will rename the database.  Takes in two parameters: Old database name and 
the new -database name.  Puts the database in single user mode and then after re-naming 
the database, sets it back to multi-user again.*/

CREATE PROCEDURE usp_RenameDB
    @OldDatabaseName varchar(50),
    @NewDatabaseName varchar(50)
WITH ENCRYPTION
AS
BEGIN
    DECLARE @OldTranCount  INTEGER;

    SET @OldTranCount = @@TRANCOUNT;

    WHILE (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION

    EXEC sp_dboption @OldDatabaseName, 'single user', 'TRUE'
    EXEC sp_renamedb @OldDatabaseName, @NewDatabaseName
    EXEC sp_dboption @NewDatabaseName, 'single user', 'FALSE'

    WHILE (@@TRANCOUNT < @OldTranCount)
        BEGIN TRANSACTION
END
GO

Rate

5 (2)

Share

Share

Rate

5 (2)