USE MASTER
GO
IF OBJECT_ID('sp_kill') IS NULL
EXEC ('CREATE PROCEDURE sp_kill AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE sp_kill
@database VARCHAR(256) = NULL,
@hostname VARCHAR(128) = NULL,
@program_name VARCHAR(128) = NULL,
@loginame VARCHAR(128) = NULL,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@PrintSQL BIT = 1,
@ExecSQL BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF NOT (ISNULL(@database, '') <> ''
OR ISNULL(@hostname, '') <> ''
OR ISNULL(@program_name, '') <> ''
OR ISNULL(@loginame, '') <> '')
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_kill',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
SELECT @SQL = Util.dbo.StringConcat('KILL ' + CAST(SPID AS VARCHAR), '
')
FROM sys.sysprocesses (NOLOCK)
WHERE (@database IS NULL
OR DB_NAME(dbid) LIKE @database)
AND (@hostname IS NULL
OR (@hostname LIKE '%[%]%'
AND hostname LIKE @hostname)
OR hostname = @hostname)
AND (@program_name IS NULL
OR (@program_name LIKE '%[%]%'
AND program_name LIKE @program_name)
OR program_name = @program_name)
AND (@loginame IS NULL
OR (@loginame LIKE '%[%]%'
AND loginame LIKE @loginame)
OR loginame = @loginame)
AND SPID <> @@SPID
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
AND @SQL <> ''
EXEC(@SQL )
GO
EXEC sys.sp_ms_marksystemobject
sp_kill
GO