DECLARE @dbName NVARCHAR(200);
SET @dbName='DB1_SNAP'
BEGIN TRY
--=====================================
--查找当前数据库所有连接并删除
DECLARE @spid INT;
DECLARE @tempSQL NVARCHAR(200);
--判断临时表是否存在，存在则删除
IF(OBJECT_ID('tempdb.dbo.#temp','U') IS NOT NULL)
BEGIN
DROP TABLE #temp
END
--查找活动的SPID
SELECT spid INTO #temp
FROM master.sys.sysprocesses
WHERE dbid =DB_ID(@dbName);
--循环并删除所有活动SPID
WHILE((SELECT COUNT(1) FROM #temp)>0)
BEGIN
SELECT TOP(1) @spid=spid FROM #temp;
SET @tempSQL='KILL '+CAST(@spid AS VARCHAR(10));
PRINT @tempSQL;
EXEC(@tempSQL);
DELETE FROM #temp WHERE spid=@spid;
END
DROP TABLE #temp
--========================================
--删除数据库
EXEC('DROP DATABASE '+@dbName);
PRINT '删除成功';
END TRY
BEGIN CATCH
PRINT '执行出错，错误信息：'
PRINT ERROR_MESSAGE()
END CATCH
