USE master
GO
IF OBJECT_ID('sp_DBDrop') IS NULL EXEC ('CREATE PROCEDURE sp_DBDrop AS SELECT 1 AS ID')
GO
ALTER PROCEDURE sp_DBDrop
@DBName VARCHAR(256) = NULL,
@PrintSQL BIT = 1,
@ExecuteSQL BIT = 0,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @DBName IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_DBDrop',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 40
END
SET @SQL = 'USE master
IF DB_ID(''' + @DBName + ''') IS NOT NULL
BEGIN
DECLARE @DropSQL VARCHAR(MAX)
SELECT @DropSQL = Util.dbo.StringConcat(''KILL '' + CAST(SPID AS VARCHAR), ''
'')
FROM sys.sysprocesses (NOLOCK)
WHERE dbid = DB_id(''' + @DBName + ''')
AND spid <> @@spid
IF @DropSQL <> '''' EXEC(@DropSQL)
ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [' + @DBName + ']
END
'
IF @PrintSQL = 1
PRINT @SQL
IF @ExecuteSQL = 1
AND @SQL <> ''
AND DB_ID(@DBName) IS NOT NULL
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject sp_DBDrop
GO