Open transactions on a database

/************************************************************************
*************************************************************************
**					The following script will show you				   **
** 	   If there are any open transactions running on your database.    **
**	    While running the below query if you find any query which	   **
**	    While running the below query if you find any query which	   **
**	is running for long time it can be killed using following command  **
**							KILL [session_id]						   **
** you could also open the query window and use the following command  **
**						  RollBack Transaction						   **
*************************************************************************
************************************************************************/
DECLARE @databasename NVARCHAR(MAX)

-- select name from master.dbo.sysdatabases
SET @databasename = ''

SELECT DMES.host_name AS 'Computer Name'
	, nt_user_name AS 'Windows Username'
	, mdsd.NAME AS 'Database with open Transaction'
	, DMES.session_id AS 'Session ID'
	, CASE 
		WHEN spn.open_tran = 0
			THEN 'There are no open transactions currently running on the' + ' ' + @databasename + ' ' + 'Database'
		WHEN spn.open_tran = 1
			THEN 'There is 1 open transaction currently running on the' + ' ' + @databasename + ' ' + 'Database'
		WHEN spn.open_tran = 2
			THEN 'There are 2 or more open transactions currently running on the' + ' ' + @databasename + ' ' + 'Database'
		WHEN spn.open_tran >= 3
			THEN 'There are 3 or more open transactions currently running on the' + ' ' + @databasename + ' ' + 'Database'
		END AS 'Open Transactions'
	, RTRIM(CAST(DMES.login_time AS NVARCHAR(30))) AS 'Date The Query window opened'
	, CASE 
		WHEN DMES.is_user_process = 0
			THEN 'No'
		WHEN DMES.is_user_process = 1
			THEN 'Yes'
		END AS 'User Process'
	, CASE 
		WHEN DMES.transaction_isolation_level = 0
			THEN 'Unspecified'
		WHEN DMES.transaction_isolation_level = 1
			THEN 'Read Uncommitted'
		WHEN DMES.transaction_isolation_level = 2
			THEN 'Read Committed'
		WHEN DMES.transaction_isolation_level = 3
			THEN 'Repeatable Read'
		WHEN DMES.transaction_isolation_level = 4
			THEN 'Serializable'
		WHEN DMES.transaction_isolation_level = 5
			THEN 'Snapshot'
		END AS 'Transaction Isolation Level'
	, CASE 
		WHEN DMES.LOCK_TIMEOUT = '-1'
			THEN 'No lock time out specified, the lock will expire when the transaction has completed'
		WHEN DMES.LOCK_TIMEOUT >= 0
			THEN ' A Lockout Time of' + ' ' + CAST(CONVERT(REAL, (DMES.LOCK_TIMEOUT) / (1000.00)) AS VARCHAR(MAX)) + ' ' + 'Seconds has been specified'
		END AS 'Lock Timeout'
FROM master..sysprocesses AS spn
JOIN sys.dm_exec_sessions AS DMES
	ON DMES.session_id = spn.spid
JOIN master.dbo.sysdatabases mdsd
	ON spn.dbid = mdsd.dbid
WHERE DMES.session_id = spn.spid
	AND mdsd.NAME = @databasename
	AND spn.open_tran <> 0