declare @subject varchar(250)
select @subject = 'Deadlock reported on ' + @@servername

EXEC msdb.dbo.sp_send_dbmail
      
       @profile_name = 'Mail_Profile_Prod',
       @recipients='*********',
       @subject = @subject,
       @body_format = 'HTML',
       @body = 'A deadlock has been recorded.  Further information can be found in the attached file.',
       @query = 'set QUOTED_IDENTIFIER on;
       CREATE TABLE #errorlog (
            LogDate DATETIME 
            , ProcessInfo VARCHAR(100)
            , [Text] VARCHAR(MAX)
            );
DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);
INSERT INTO #errorlog EXEC sp_readerrorlog;
SELECT @tag = text
FROM #errorlog 
WHERE [Text] LIKE ''Logging%MSSQL\Log%'';
DROP TABLE #errorlog;
SET @path = SUBSTRING(@tag, 38, CHARINDEX(''MSSQL\Log'', @tag) - 29);
SELECT TOP 1 
  CONVERT(xml, event_data).query(''/event/data/value/child::*'') AS DeadlockReport,
  CONVERT(xml, event_data).value(''(event[@name="xml_deadlock_report"]/@timestamp)[1]'', ''datetime'') 
  AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + ''\system_health*.xel'', NULL, NULL, NULL)
WHERE OBJECT_NAME like ''xml_deadlock_report''
ORDER BY Execution_Time DESC',
      @query_result_width = 32767
      @attach_query_result_as_file = 1

CREATE TABLE #errorlog (
            LogDate DATETIME 
            , ProcessInfo VARCHAR(100)
            , [Text] VARCHAR(MAX)
            );
DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX), @deadlock xml, @body1 VARCHAR(MAX);
INSERT INTO #errorlog EXEC sp_readerrorlog;
SELECT @tag = text
FROM #errorlog 
WHERE [Text] LIKE 'Logging%MSSQL\Log%';
DROP TABLE #errorlog;

----------------

CREATE TABLE #res (
            DeadlockReport xml, 
            LogDate DATETIME
                   ); 
-------------------
SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

insert into #res
SELECT top 1
  CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
  CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') 
  AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report'
ORDER BY Execution_Time DESC;

set @deadlock = (select [DeadlockReport] from #res)
--PRINT 'Deadlock: ' + CONVERT(VARCHAR(MAX), @deadlock);
set @body1 = CONVERT(VARCHAR(MAX), @deadlock)
print @body1
-------------------

declare @subject varchar(250)
select @subject = 'Deadlock reported on ' + @@servername

EXEC msdb.dbo.sp_send_dbmail
	  
       @profile_name = 'Mail_Profile_Prod',
       @recipients='***************',
       @subject = @subject,
	   @body_format = 'HTML',
       @body = @body1
	   
   --    @query = 'set QUOTED_IDENTIFIER on; 	   print @deadlock;',
     -- @query_result_width = 32767,
     -- @attach_query_result_as_file = 1 
-------------------
DROP TABLE #res;