Mike S-G has passed me this useful tool for dropping all connections from a database. This is required before deleting or re-naming databases, for example.
This creates a stored procedure in the “master” database, which can then be called to operate on any one database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_killprocess] Script Date: 09/18/2012 14:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_killprocess] @dbname VARCHAR(128)
AS
SET nocount ON
SET quoted_identifier OFF
DECLARE @kill_id INT
DECLARE @query VARCHAR(320)
DECLARE killprocess_cursor CURSOR
FOR
SELECT a.spid
FROM sysprocesses a
JOIN sysdatabases b ON a.dbid = b.dbid
WHERE b.name = @dbname
OPEN killprocess_cursor
FETCH NEXT FROM killprocess_cursor INTO @kill_id
WHILE ( @@fetch_status = 0 )
BEGIN
SET @query = 'kill ' + CONVERT(VARCHAR, @kill_id)
EXEC (@query)
FETCH NEXT FROM killprocess_cursor INTO @kill_id
END
CLOSE killprocess_cursor
DEALLOCATE killprocess_cursor
--usage
--exec usp_killprocess "mydatabasename"
Example usage:
usp_killprocess myDatabaseName