here's a way to rename, or force a database drop. i'm not sure if you need or want the rollback transaction lines. but here you go...
also on renaming, you could dettach the database, move the data / log files, then reattach.
/*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
/*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