1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

CREATE PROCEDURE [dbo].[dmp_send_email_HTML_format]
@objName VARCHAR(255)
, @Subject VARCHAR(255)
, @PreDescription varchar(8000) = NULL
, @Recipients VARCHAR(8000)
, @importance VARCHAR(255) = 'High'
, @profile VARCHAR(255) = 'Public_Profile'  --eMail profile name
as
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	/*
	Return value @r
	10001 : object is wrong
	10002 : column is wrong
	0 : Done or Nodata.
	*/
	DECLARE @tableHTML NVARCHAR(max)
		  , @HTML_TH NVARCHAR(max)
		  , @HTML_TD NVARCHAR(max)
		  , @sql NVARCHAR(4000)
		  , @param NVARCHAR(4000)
		  , @DBName VARCHAR(255)
		  , @rowcnt INT
		  , @r INT
	DECLARE @tbl_datacheck TABLE (i INT)
	SET @DBName = PARSENAME(@objName,3)
	SET @r = -1
	IF OBJECT_ID(@objName) IS NULL BEGIN
		SET @r = 10001
		GOTO ERROR;
	END
	IF @DBName = " OR @DBName IS NULL BEGIN
		SET @DBName = DB_NAME()
	END
	SET @sql = 'select top 1 1 from '+ @objName
	INSERT INTO @tbl_datacheck
	EXEC (@sql)
	SET @rowcnt = @@ROWCOUNT
	IF @rowcnt <=0 BEGIN
		SET @r = 0
		GOTO ERROR;
	END
	IF OBJECT_ID('tempdb.dbo.#tbl_column') IS NOT NULL
		DROP TABLE #tbl_column
	CREATE TABLE #tbl_column (idx INT IDENTITY(1,1), NAME VARCHAR(255))
	set @sql = '
				INSERT #tbl_column
				SELECT name
				  FROM '+@DBName+'.SYS.syscolumns
				 WHERE ID = OBJECT_ID(@objName)
				 ORDER BY colorder
				'
	set @param = ' @objName varchar(255) '
	exec sp_executesql @sql, @param, @objName
	SET @rowcnt = @@ROWCOUNT
	--PRINT @rowcnt
	IF @rowcnt = 0 BEGIN
		SET @r = 10002
		GOTO ERROR;
	END
	SELECT @HTML_TH =
		   CAST ((SELECT th = isnull(NAME,' ')
					FROM #tbl_column
				   FOR XML PATH(''), TYPE
				 ) AS NVARCHAR(MAX) )
	--print @HTML_TH
	SET @HTML_TD = ''
	SELECT @HTML_TD = @HTML_TD + 'td = isnull('+NAME+','' ''), '''',  '
	  FROM #tbl_column
	SET @HTML_TD = SUBSTRING(@HTML_TD, 1, LEN(@HTML_TD)-1)
	SET @sql = 'SELECT @HTML_TD = CAST ((SELECT '+ REPLACE(@HTML_TD,'','''') +'
								 FROM '+@objName+'
								  FOR XML PATH(''tr''), TYPE
							) AS NVARCHAR(MAX) )
				'
	--PRINT @sql
	EXEC sp_executesql @sql, N' @HTML_TD NVARCHAR(MAX) OUTPUT ', @HTML_TD OUTPUT
	--PRINT @HTML_TD
	set @Recipients = isnull(@Recipients,'NowcomDBDevTeam@nowcom.com')
	set @subject = @@servername +' -- ' + convert(varchar(255), getdate(), 101) + ' ' + convert(varchar(5), getdate(), 108)+' '  + @Subject
	SET @tableHTML = '
	h1{
	font-size:17px;
	}
	body{
	font-size:15px;
	}
	table{
	border-collapse:collapse;
	border:1px solid black;
	font-size:12px;
	}
	table th{
	font:bold;
	border:1px solid black;
	padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px;
	background-color:lightblue;
	font-size:12px;
	}
	table td{
	border:1px solid black;
	padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px
	font-size:12px;
	}
 '+ @subject +'
	'+ ISNULL(@PreDescription,") + '
'
		+ @HTML_TD
	set @tableHTML = @tableHTML + '

	'
	SELECT @tableHTML = 'From Server :' + ISNULL(@@SERVERNAME, ") + char(13) + char(10) + ISNULL(@tableHTML, ");
	EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile
								, @body_format = 'HTML'
								, @recipients = @Recipients
								, @importance = @importance
								, @copy_recipients ="
								, @subject = @subject
								, @body = @tableHTML
	IF @@error  0 BEGIN
		RAISERROR('Error in dmp_send_email_HTML_format. Failed to send Database Mail.', 16, 1);
	END
	RETURN 0
ERROR:
	return @r
END
Go