create proc dbo.get_lock_report
as
begin

	while 1=1
	begin

		-- sleep for 4 seconds
		waitfor delay '00:00:04'
		
		SELECT
			spid = Lock.request_session_id,
			HostName = Session.host_name,
			ProgramName = Session.program_name,
			Cmd = Request.Command,
			LoginName = Session.login_name,
			dbid = Lock.resource_database_id,
			DatabaseName = DB_NAME(Lock.resource_database_id),
			Type = Lock.resource_type,
			LockType = Lock.request_type,
			Mode = Lock.request_mode,
			Status = request_status,
			UserTable.Name
			
		FROM
			sys.dm_tran_locks AS Lock

			LEFT JOIN
			sys.dm_exec_sessions AS Session ON
				Session.session_id = Lock.request_session_id

			LEFT JOIN
			sys.dm_exec_requests AS Request ON
				Request.session_id = Session.session_id

			LEFT JOIN
			sys.tables AS UserTable ON
				UserTable.object_id = Lock.resource_associated_entity_id
				AND
				type = 'U'

		WHERE
			Lock.resource_type = 'OBJECT'

	end
end
go