SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_FolderExist]')) DROP FUNCTION [dbo].[fun_FolderExist];
GO


CREATE FUNCTION [dbo].[fun_FolderExist] (
  @dirname VARCHAR(265))
RETURNS BIT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if folder exists.

Return value (BIT):
 0 (False) - Folder does not exist
 1 (True)  - Folder exists

EXAMPLES
SELECT dbo.fun_FolderExist('C:\Program Files')
1

SELECT dbo.fun_FolderExist('C:\xxxx')
0

-- IIF is available starting with SQL Server 2012
SELECT IIF(dbo.fun_FolderExist('C:\Program Files')=1,'Yes','No')
Yes

HISTORY
2015-11-24 - Created function
2024-02-09 - Changed history table
2026-01-23 - Changed return value type to BIT
2026-03-30 - Changed examples

TAGS
<program>
  <description>Check if folder exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofolder INT
  DECLARE @name VARCHAR(265)
  DECLARE @result BIT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FolderExists', @ofolder OUT, @dirname;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofolder;
  RETURN @result;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_format_tablename       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_format_tablename]')) DROP FUNCTION [dbo].[fun_format_tablename];
GO


CREATE FUNCTION [dbo].[fun_format_tablename] (
  @table_name NVARCHAR(255))
RETURNS NVARCHAR(255) AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Format tablename.

Database_name, schema_name and table_name parts are surrounded by []
Function returns NULL if input table_name is NULL or ''.

EXAMPLES
SELECT dbo.fun_format_tablename('TEST')
[TEST]
SELECT dbo.fun_format_tablename('dbo.TEST')
[dbo].[TEST]
SELECT dbo.fun_format_tablename('DBMAINT.dbo.TEST')
[DBMAINT].[dbo].[TEST]
SELECT dbo.fun_format_tablename('#tmp_folders')
[#tmp_folders]
SELECT dbo.fun_format_tablename('')
NULL
SELECT dbo.fun_format_tablename(NULL)
NULL

HISTORY
2025-12-04 - Created function

TAGS
<program>
  <description>Format tablename</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2025-12-04</created>
  <lastmodified>2025-12-04</lastmodified>
</program>
*/
  DECLARE @tname NVARCHAR(128)
  DECLARE @sname NVARCHAR(128)
  DECLARE @dname NVARCHAR(128)
  DECLARE @oname NVARCHAR(255)

  SET @tname = PARSENAME(@table_name,1);
  SET @sname = PARSENAME(@table_name,2);
  SET @dname = PARSENAME(@table_name,3);

  IF @tname IS NOT NULL
  BEGIN
    IF @sname IS NOT NULL
    BEGIN
      IF @dname IS NOT NULL
      BEGIN
        SET @oname = '['+@dname+'].['+@sname+'].['+@tname+']'
      END
      ELSE
      BEGIN
        SET @oname = '['+@sname+'].['+@tname+']'
      END
    END
    ELSE
    BEGIN
      IF @dname IS NOT NULL
      BEGIN
        SET @oname = '['+@dname+']..['+@tname+']'
      END
      ELSE
      BEGIN
        SET @oname = '['+@tname+']'
      END
    END
  END
  ELSE
  BEGIN
    SET @oname = NULL;
  END

  RETURN @oname;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_getfield       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_getfield]')) DROP FUNCTION [dbo].[fun_getfield];
GO


CREATE FUNCTION [dbo].[fun_getfield] (
  @str VARCHAR(MAX),
  @nth INT,
  @pat VARCHAR(255))
RETURNS VARCHAR(MAX)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Return the n-th field from a given string, between a given separator pattern

The given separator pattern may be max 255 long.
The separator pattern can not be space (or spaces) only, use the function fun_getword in stead.

EXAMPLE
SELECT dbo.fun_getfield('a,,b,c',3,',')
b

HISTORY
2000-01-01 - Created function
2024-02-09 - Changed history table

TAGS
<program>
  <description>Return the n-th field from a given string, between a given separator pattern</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2000-01-01</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @loc INT
  DECLARE @cnt INT
  DECLARE @field VARCHAR(MAX)
  
  SET @str = ISNULL(@str,'');
  SET @pat = ISNULL(@pat,'');
  
  SET @loc = 0;
  SET @cnt = 0;
  SET @field = NULL;
  
  IF @str <> '' AND @pat <> '' AND @nth > 0
  BEGIN
    -- Loop through fields
    WHILE 1<>2
    BEGIN
      SET @loc = CHARINDEX(@pat, @str);
      IF @loc = 0
      BEGIN
        -- Pattern not found (anymore)
        IF @cnt+1 = @nth
        BEGIN
          -- Field is rest
          SET @field = @str;
        END
        BREAK
      END
      ELSE
      BEGIN
        -- Pattern found (again)
        SET @cnt = @cnt + 1;
        IF @cnt = @nth
        BEGIN
          -- Field found
          SET @field = LEFT(@str, @loc-1);
          BREAK
        END
        -- New string is rest
        -- LEN is not usable here, it counts the number of characters in the string (excluding trailing blanks)
        -- DATALENGTH gives number of bytes, for doublebyte datatypes bytes*2 per character.
        SET @str = RIGHT(@str, DATALENGTH(@str)-@loc-DATALENGTH(@pat)+1);
      END
    END
  END
  RETURN @field;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_greatest_date2       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_greatest_date2]')) DROP FUNCTION [dbo].[fun_greatest_date2];
GO


CREATE FUNCTION [dbo].[fun_greatest_date2] (
  @a DATETIME,
  @b DATETIME) 
RETURNS DATETIME
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Returns the greatest of 2 dates.

EXAMPLES
1)
SELECT dbo.fun_greatest_date2('2018-06-13 13:00:00','2018-06-13 13:00:10')
2018-06-13 13:00:10.000

2)
SELECT dbo.fun_greatest_date2('2018-06-13 13:00:00',NULL)
2018-06-13 13:00:00.000

3)
SELECT dbo.fun_greatest_date2(NULL,NULL)
NULL

HISTORY
2000-01-01 - Created function
2015-01-28 - NULL input is now correct handled
2018-06-13 - Use table variable to compare dates
2024-02-09 - Changed history table

TAGS
<program>
  <description>Returns the greatest of 2 dates</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2000-01-01</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @result DATETIME
  DECLARE @tab TABLE ([value] DATETIME);

  IF @a IS NOT NULL INSERT INTO @tab ([value]) VALUES (@a);
  IF @b IS NOT NULL INSERT INTO @tab ([value]) VALUES (@b);

  IF (@a IS NULL AND @b IS NULL)
    SET @result = NULL;
  ELSE
    SET @result = (SELECT MAX([value]) FROM @tab);

  RETURN @result;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_greatest_date3       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_greatest_date3]')) DROP FUNCTION [dbo].[fun_greatest_date3];
GO


CREATE FUNCTION [dbo].[fun_greatest_date3] (
  @a DATETIME,
  @b DATETIME,
  @c DATETIME) 
RETURNS DATETIME
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Returns the greatest of 3 dates.

EXAMPLES
1)
SELECT dbo.fun_greatest_date3('2018-06-13 13:00:00','2018-06-13 13:00:10','2018-06-13 13:00:20')
2018-06-13 13:00:20.000

2)
SELECT dbo.fun_greatest_date3('2018-06-13 13:00:00','2018-06-13 13:00:10',NULL)
2018-06-13 13:00:10.000

3)
SELECT dbo.fun_greatest_date3('2018-06-13 13:00:00',NULL,NULL)
2018-06-13 13:00:00.000

SELECT dbo.fun_greatest_date3(NULL,NULL,NULL)
NULL

HISTORY
2018-06-13 - Created function
2024-02-09 - Changed history table

TAGS
<program>
  <description>Returns the greatest of 3 dates</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2018-06-13</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @result DATETIME
  DECLARE @tab TABLE ([value] DATETIME);

  IF @a IS NOT NULL INSERT INTO @tab ([value]) VALUES (@a);
  IF @b IS NOT NULL INSERT INTO @tab ([value]) VALUES (@b);
  IF @c IS NOT NULL INSERT INTO @tab ([value]) VALUES (@c);

  IF (@a IS NULL AND @b IS NULL AND @c IS NULL)
    SET @result = NULL;
  ELSE
    SET @result = (SELECT MAX([value]) FROM @tab);

  RETURN @result;
END


GO

/****** Object:  SQL_TABLE_VALUED_FUNCTION  fun_nsplit       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_nsplit]')) DROP FUNCTION [dbo].[fun_nsplit];
GO


CREATE FUNCTION [dbo].[fun_nsplit] (
  @text NVARCHAR(MAX),
  @delimiter NVARCHAR(10))
RETURNS @strings TABLE (
  position INT IDENTITY PRIMARY KEY,
  [value] NVARCHAR(MAX) )
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Split delimited n-string into record values.

This UDF is based upon fn_Split described in https://odetocode.com/articles/365.aspx

The output values are left and right trimmed.

EXAMPLES
1)
SELECT [position], [value] FROM dbo.fun_nsplit(N'foo,bar,widget', N',')
1        foo
2        bar
3        widget

2)
SELECT [position], [value] FROM dbo.fun_nsplit(N'foo,bar,,widget', N',')
position value
1        foo
2        bar
3        
4        widget

3)
SELECT [position], [value] FROM dbo.fun_nsplit(N'foo,bar,widget,', N',')
position value
1        foo
2        bar
3        widget
4        

HISTORY
2005-03-21 - Created function
2018-01-22 - Limited @delimiter to NVARCHAR(1)
           - Changed table variable @Strings to @strings
2024-02-09 - Changed history table
2024-02-14 - Extended @delimiter to NVARCHAR(10)
             Fixed bug: Last empty value was not detected
2024-04-09 - Correct NULL delimiter

TAGS
<program>
  <description>Split delimited n-string into record values</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2005-03-21</created>
  <lastmodified>2024-04-09</lastmodified>
</program>

*/
  DECLARE @index INT
  DECLARE @delimlen INT

  SET @index = -1;
  IF @delimiter IS NULL SET @delimiter = ',';
  SET @delimlen = DATALENGTH(@delimiter);
  SET @text = @text + @delimiter;

  WHILE (DATALENGTH(@text)/2 > 0)
  BEGIN
    SET @index = CHARINDEX(@delimiter, @text);
    IF (@index = 0) AND (DATALENGTH(@text)/2 > 0)
    BEGIN
      INSERT INTO @strings VALUES (RTRIM(LTRIM(@text)));
      BREAK
    END
    IF (@index > 1)
    BEGIN
      INSERT INTO @strings VALUES (RTRIM(LTRIM(LEFT(@text, @index - 1))));
    END
    ELSE
    BEGIN
      INSERT INTO @strings VALUES ('');
    END
    SET @text = RIGHT(@text, (DATALENGTH(@text)/2 - @index - @delimlen/2 + 1));
  END
  RETURN
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_sqlversion       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_sqlversion]')) DROP FUNCTION [dbo].[fun_sqlversion];
GO


CREATE FUNCTION [dbo].[fun_sqlversion] () 
RETURNS NUMERIC(5,1)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Returns the sqlversion.

The returned format is numeric(5,1), like 16.0.

EXAMPLES
SELECT dbo.fun_sqlversion()
16.0

HISTORY
2026-03-29 - Created function

TAGS
<program>
  <description>Returns the sqlversion</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2026-03-29</created>
  <lastmodified>2026-03-29</lastmodified>
</program>
*/
  DECLARE @result NUMERIC(5,1)
  SET @result = CONVERT(NUMERIC(5,1), (@@MICROSOFTVERSION / 0x01000000) + ((@@MICROSOFTVERSION & 0x0FFF000) /0x010000) /100.0);
  RETURN @result;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_text2html       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_text2html]')) DROP FUNCTION [dbo].[fun_text2html];
GO



CREATE FUNCTION [dbo].[fun_text2html] (
  @str AS NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX) AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Replace HTML/XML reserved characters in given text to HTML entity names. 
Also replace CrLf and Lf to <br>
Return '' if input string is NULL.

CHAR(34)          to '&quot;'  (" Double quotes or speech marks)
CHAR(38)          to '&amp;'   (& Ampersand)
CHAR(39)          to '&apos;'  (' Single quote)
CHAR(60)          to '&lt;'    (< Less than or open angled bracket)
CHAR(62)          to '&gt;'    (> Greater than or close angled bracket)
CHAR(13)+CHAR(10) to '<br>     (  Carriagereturn + Linefeed)
CHAR(10)          to '<br>     (  Linefeed)

Article for reference:
https://www.html.am/reference/html-special-characters.cfm

EXAMPLES
SELECT dbo.fun_text2html('<Gerrit & Wilma>')
&lt;Gerrit &amp; Wilma&gt;
����
HISTORY
2014-10-22 - Created function
2021-12-24 - Added characters to the 128-159 range
2024-02-09 - Changed history table
2025-06-08 - Replace HTML/XML reserved characters only
2025-11-26 - Changed input and output fieldtype from VARCHAR to NVARCHAR
           - Added replacement of CrLf and Lf

 TAGS
<program>
  <description>Replace HTML/XML reserved characters in given text to HTML entity names</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2014-10-22</created>
  <lastmodified>2025-11-26</lastmodified>
</program>
*/
  DECLARE @text NVARCHAR(MAX)

  SET @text = ISNULL(@str,'');

  -- Replace & first, preventing double replacements
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(38),'&amp;');
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(34),'&quot;');
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(39),'&apos;');
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(60),'&lt;');
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(62),'&gt;');
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(13)+CHAR(10),'<br>')
  SET @text = REPLACE(@text COLLATE Latin1_General_BIN,CHAR(10),'<br>')
  RETURN @text;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_uniquename       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_uniquename]')) DROP FUNCTION [dbo].[fun_uniquename];
GO


CREATE FUNCTION [dbo].[fun_uniquename] ()
RETURNS VARCHAR(25)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Return an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn.

yyyy=year, mm=month, dd=day, hh=hour(24), mi=minutes, ss=seconds, nnnnnnn=nanoseconds

This function can be used to generate a unique name for a backupfile.
See example.

EXAMPLE
SELECT 'TEST_backup_' + dbo.fun_uniquename() + '.bak'
TEST_backup_2024_02_10_164919_7874167.bak

HISTORY
2000-01-01 - Created function
2024-02-09 - Changed history table

TAGS
<program>
  <description>Return an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2000-01-01</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @time DATETIME2
  DECLARE @uniquename VARCHAR(25)

  SET @time = SYSDATETIME();
  SET @uniquename = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR, @time, 20),'-','_'),':',''),' ','_') +
    '_' +  RIGHT('0000000'+CONVERT(NVARCHAR,DATEPART(NANOSECOND, @time)/100),7);

  RETURN @uniquename;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_save_text_to_utf8file       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_save_text_to_utf8file]')) DROP PROCEDURE [dbo].[prc_save_text_to_utf8file];
GO




CREATE PROCEDURE [dbo].[prc_save_text_to_utf8file] (
  @text NVARCHAR(MAX), 
  @filename VARCHAR(265))
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Write text variable to utf-8 file with BOM.

OLE Automation Procedures has to be enabled.
Existing files will be overwritten.
If path does not exist, no error is raised. Check path before with function fun_FolderExist.
If file could not be created, no error is raised. Check file afterwards with function fun_FileExist.

Parameters
1 @text       VARCHAR(MAX)  = (nonbinary) text
2 @filename   VARCHAR(265)  = filespec

HISTORY
2025-10-12 - Created procedure

TAGS
<program>
  <description>Write text variable to utf-8 file with BOM</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2025-10-12</created>
  <lastmodified>2025-10-12</lastmodified>
</program>
*/
  DECLARE @objtoken INT

  EXEC sp_OACreate 'ADODB.Stream', @objtoken OUTPUT;
  EXEC sp_OASetProperty @objtoken, 'Type', 2;
  EXEC sp_OASetProperty @objtoken, 'ContentType', 'text/plain'
  EXEC sp_OASetProperty @objtoken, 'Encoding', 'quoted-printable'
  EXEC sp_OASetProperty @objtoken, 'Charset', 'utf-8'
  EXEC sp_OAMethod @objtoken, 'Open';
  EXEC sp_OAMethod @objtoken, 'WriteText', NULL, @text;
  EXEC sp_OAMethod @objtoken, 'SaveToFile', NULL, @filename, 2;
  EXEC sp_OAMethod @objtoken, 'Close';
  EXEC sp_OADestroy @objtoken;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_table2html       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_table2html]')) DROP PROCEDURE [dbo].[prc_table2html];
GO


CREATE PROCEDURE [dbo].[prc_table2html] (
  @table_name NVARCHAR(255),
  @whereclause NVARCHAR(255) = NULL,
  @orderfields NVARCHAR(255) = NULL,
  @skipfields NVARCHAR(255) = NULL,
  @redfields NVARCHAR(255) = NULL,
  @boldfields NVARCHAR(255) = NULL,
  @italicfields NVARCHAR(255) = NULL,
  @urlfields NVARCHAR(255) = NULL,
  @urlimages VARCHAR(255) = NULL,
  @urlstrings VARCHAR(255) = NULL,
  @width VARCHAR(255) = NULL,
  @bgcolor VARCHAR(255) = NULL,
  @sortable INT = 0,
  @rotate INT = 0,
  @numleft INT = 0,
  @html NVARCHAR(MAX) OUTPUT)
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Generate HTML table for a given table or view.

This procedure uses the SELECT FOR XML PATH method.
The following fieldtypes cannot not be processed and are shown as <Binary data>: image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp, xml
This procedure returns NULL if no rows are selected.
The local variables @tag1, @tag2, @tag3 and @tag4 contain strings that are reserved words in VARCHAR or NVARCHAR tabledata.
Table- and fieldname parameter values may optionally be enclosed by [].

PARAMETERS
1  @table_name   NVARCHAR(255)         Table- or viewname. Temp tables are allowed.
2  @whereclause  NVARCHAR(255)         Optional string containing the WHERE clause to filter rows. Must begin with 'WHERE '.
3  @orderfields  NVARCHAR(255)         Optional comma separated list of fieldnames to order rows (ascending). For descending sortorder add ' DESC' to fieldnames.
4  @skipfields   NVARCHAR(255)         Optional comma separated list of fieldnames to skip.
5  @redfields    NVARCHAR(255)         Optional comma separated list of fieldnames to make cell data font color red.
6  @boldfields   NVARCHAR(255)         Optional comma separated list of fieldnames to make cell data font bold.
7  @italicfields NVARCHAR(255)         Optional comma separated list of fieldnames to make cell data font italic.
8  @urlfields    NVARCHAR(255)         Optional comma separated list of fieldnames to make cell data a link. Urlfields must be of type VARCHAR or NVARCHAR/SYSNAME otherwise ignored.
9  @urlimages    VARCHAR(255)          Optional comma separated list of images to replace urlfields links. The number or elements in urlfields and urlimages must match. Prevails above urlstrings.
10 @urlstrings   VARCHAR(255)          Optional comma separated list of strings to replace urlfield links. The number or elements in urlfields and urlstrings must match.
11 @width        VARCHAR(255)          Optional comma separated values for tablewidth in pixels or % and (optional) columnwidth of first column when @rotate=1 in pixels or % (*).
12 @bgcolor      VARCHAR(255)          Optional comma separated values for table background color and (optional) color for highlighting even rows (**).
13 @sortable     INT             0/1/2 Tablecolumns are not sortable (default) / sortable / sortable with arrows (***).
14 @rotate       INT             0/1   Table is not rotated (default) / rotated. When rotated, both sortable and 2-nd bgcolor are disabled.
15 @numleft      INT             0/1   Numeric fields are right aligned (default) / left aligned.
16 @html         NVARCHAR(MAX) OUTPUT  Output string containing the HTML text.

(*) If the table does not fit within the given width, the browser uses a width as needed for all columns to display.

(**) Don't use dark background colors as Black or DarkGray, but light colors. See https://en.wikipedia.org/wiki/Web_colors
Proposed color combinations for background and row-highlighting are:
- LightYellow, Wheat
- LightCyan, LightBlue
- White, WhiteSmoke
- GhostWhite, Lavender

(***) To implement sortable columns:
Download the java script table-sort.js from https://www.gerritmantel.nl/html_public/prog/scripts/table-sort.js
- A script line in the html <head> part must point to the java script,
  e.g.: <script src="table-sort.js"></script>
- Use @sortable=1 to implement table-sort
Or - download rhw latest version of the java script table-sort.js from https://github.com/kyle-wannacott/table-sort-js
- Use @sortable=1 to implement table-sort
- Use @sortable=2 to implement table-sort with arrows

The HTML output text contains 2 sections: <style> and <table>.
The style has a unique class name exclusively for this table and contains the sections:
 - table              -> border 1px solid DarkGray, background-color: [1-st bgcolor] (only when @bgcolor has first color), border-collapse: collapse
 - th/td              -> padding 3px
 - tr:nth-child(even) -> background-color: [2-nd bgcolor] (only when @bgcolor has second color)
 The table layout can have 2 possible formats:
 - not rotated        -> the headerrow contains table fieldnames and has a fixed background color LightGray
 - rotated            -> the first column contains table fieldnames and has a fixed background color LightGray

EXAMPLES
-- The following example uses two bgcolors. 
-- The first is used for table background, the second for row highlighting.
DECLARE @html NVARCHAR(MAX)
EXEC [dbo].[prc_table2html]
  @table_name = 'TEST',
  @bgcolor = 'LightYellow, Wheat',
  @html = @html OUTPUT;
SELECT @html

-- The following example uses two urlfields.
-- If urlimages and/or urlstrings are used, they must have the same amount of elements (two).
-- If both urlimages and urlstrings are used for the same elements, urlimages prevail above urlstrings
DECLARE @html NVARCHAR(MAX)
EXEC [dbo].[prc_table2html]
  @table_name = 'TEST',
  @whereclause = 'WHERE Name = ''gerrit''',
  @orderfields = 'Name',
  @skipfields = 'Id',
  @redfields = 'Member',
  @urlfields = 'Url1, Url2',
  @urlimages = '../images/home.png,',
  @urlstrings = ',Link',
  @bgcolor = 'LightCyan',
  @html = @html OUTPUT;
SELECT @html

USED OBJECTS
- dbo.fun_format_table_name (UDF in local database)
- dbo.fun_uniquename (UDF in local database)
- dbo.fun_getfield (UDF in local database)
- dbo.fun_nsplit (UDF in local database) (STRING_SPLIT is avoided because the lack of ordering and trimming)
- dbo.fun_text2html (UDF in local database)
- dbo.fun_string_strip (UDF in local database)
- table-sort.js (External java script, only needed when @sortable=1 or @sortable=2. See parameter @sortable description for implementation)

HISTORY
2025-11-23 - Created procedure
2025-12-01 - Use SELECT FOR XML method
2025-12-03 - Implemented sortorder
2025-12-05 - Corrected handling temp table notations
2026-01-02 - Added tr class with name = bgcolor to non-rotated tables
2026-01-03 - Width and bgcolor parameters are now VARCHAR(255)
2026-01-15 - Added vertical-align:top to style of first column on rotated tables
2026-01-28 - Fixed bug: when rotate=1 and orderfields is not null, ordering does not work
           - Fixed bug: when bgcolor is NULL and rotate=0 (or NULL), the output is NULL
           - Fixed bug: when table has binary datatypes, column header fields where missing.
           - Implemented style section in HTML output
           - Fixed error: STRING_AGG aggregation result exceeded the limit of 8000 bytes
2026-01-29 - Parameter @urlimages and @urlstrings can have empty elements, see example 2.
2026-02-07 - Added vertical-align:top to th and td (every) style (not depending on rotated anymore)

TAGS
<program>
  <description>Generate HTML table for a given table or view</description>
  <minversion>14</minversion>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2025-11-23</created>
  <lastmodified>2026-02-07</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @query NVARCHAR(MAX) = N''
  DECLARE @style NVARCHAR(MAX) = N''
  DECLARE @table NVARCHAR(MAX) = N''
  DECLARE @thead NVARCHAR(MAX) = N''
  DECLARE @tbody NVARCHAR(MAX) = N''
  DECLARE @columnlist1 NVARCHAR(MAX) = N''
  DECLARE @columnlist2 NVARCHAR(MAX) = N''
  DECLARE @select NVARCHAR(MAX) = N''
  DECLARE @sqlcmd NVARCHAR(MAX) = N''
  DECLARE @message VARCHAR(512) = ''
  DECLARE @table_fields TABLE (
    [column_ordinal] INT,
    [name] NVARCHAR(128),
    [system_type_id] INT,
    [error_number] INT,
    [orderfield] INT,
    [sortorder] VARCHAR(4),
    [redfield] INT,
    [boldfield] INT,
    [italicfield] INT,
    [urlfield] INT,
    [skipfield] INT,
    [urlimage] VARCHAR(255),
    [urlstring] VARCHAR(255));
  DECLARE @width1 VARCHAR(255)
  DECLARE @width2 VARCHAR(255)
  DECLARE @bgcolor1 VARCHAR(255)
  DECLARE @bgcolor2 VARCHAR(255)
  DECLARE @table_width NVARCHAR(50)
  DECLARE @firstcol_width NVARCHAR(50)
  DECLARE @table_color NVARCHAR(50)
  DECLARE @evenrow_color NVARCHAR(50)
  DECLARE @numalign NVARCHAR(5)
  DECLARE @colno INT
  DECLARE @colname NVARCHAR(128)
  DECLARE @table_class NVARCHAR(128)
  DECLARE @row NVARCHAR(MAX) = N''
  DECLARE @crlf VARCHAR(2)
  DECLARE @rec_cnt INT
  DECLARE @tag1 NVARCHAR(4)
  DECLARE @tag2 NVARCHAR(4)
  DECLARE @tag3 NVARCHAR(4)
  DECLARE @tag4 NVARCHAR(4)

  SET @crlf = CHAR(13) + CHAR(10);
  SET @rec_cnt = 0;
  SET @tag1 = N'@@@1';
  SET @tag2 = N'@@@2';
  SET @tag3 = N'@@@3';
  SET @tag4 = N'@@@4';

  -- Trim parameters
  SET @table_name = LTRIM(RTRIM(@table_name));
  SET @whereclause = LTRIM(RTRIM(@whereclause));
  SET @orderfields = LTRIM(RTRIM(REPLACE(REPLACE(@orderfields,'[',''), ']','')));
  SET @skipfields = LTRIM(RTRIM(REPLACE(REPLACE(@skipfields,'[',''), ']','')));
  SET @redfields = LTRIM(RTRIM(REPLACE(REPLACE(@redfields,'[',''), ']','')));
  SET @boldfields = LTRIM(RTRIM(REPLACE(REPLACE(@boldfields,'[',''), ']','')));
  SET @italicfields = LTRIM(RTRIM(REPLACE(REPLACE(@italicfields,'[',''), ']','')));
  SET @urlfields = LTRIM(RTRIM(REPLACE(REPLACE(@urlfields,'[',''), ']','')));
  SET @width = LTRIM(RTRIM(@width));
  SET @bgcolor = LTRIM(RTRIM(@bgcolor));

  -- Correct parameters
  SET @table_name = dbo.fun_format_tablename(@table_name);
  -- System function sys.dm_exec_describe_first_result_set gives message if temp tables are specified with tempdb.. database name, so strip off
  IF @table_name LIKE '/[tempdb/]../[#%' ESCAPE '/' SET @table_name = REPLACE(@table_name,'[tempdb]..','');
  IF LEN(@whereclause) = 0 SET @whereclause = NULL;
  IF LEN(@orderfields) = 0 SET @orderfields = NULL;
  IF LEN(@skipfields) = 0 SET @skipfields = NULL;
  IF LEN(@redfields) = 0 SET @redfields = NULL;
  IF LEN(@boldfields) = 0 SET @boldfields = NULL;
  IF LEN(@italicfields) = 0 SET @italicfields = NULL;
  IF LEN(@urlfields) = 0 SET @urlfields = NULL;
  IF LEN(@urlimages) = 0 SET @urlimages = NULL;
  IF LEN(@urlstrings) = 0 SET @urlstrings = NULL;
  IF @sortable IS NULL SET @sortable = 0;
  IF @rotate IS NULL SET @rotate = 0;
  IF @rotate = 1 SET @sortable = 0;
  IF @numleft IS NULL SET @numleft = 0;
  SET @width1 = LTRIM(RTRIM(dbo.fun_getfield(@width,1,',')));
  SET @width2 = LTRIM(RTRIM(dbo.fun_getfield(@width,2,',')));
  SET @bgcolor1 = LTRIM(RTRIM(dbo.fun_getfield(@bgcolor,1,',')));
  SET @bgcolor2 = LTRIM(RTRIM(dbo.fun_getfield(@bgcolor,2,',')));
  IF LEN(@width1) = 0 SET @width1 = NULL;
  IF LEN(@width2) = 0 SET @width2 = NULL;
  IF LEN(@bgcolor1) = 0 SET @bgcolor1 = NULL;
  IF LEN(@bgcolor2) = 0 SET @bgcolor2 = NULL;
  IF @width1 IS NULL AND @width2 IS NOT NULL SET @width2 = null;
  IF @bgcolor1 IS NULL AND @bgcolor2 IS NOT NULL SET @bgcolor2 = null;

  -- Set table class to unique name: tyyyy_mm_dd_hhmiss_nnnnnnn (yyyy=year, mm=month, dd=day, hh=hour(24), mi=minutes, ss=seconds, nnnnnnn=nanoseconds)
  SET @table_class = 't'+dbo.fun_uniquename();

  -- Set table-width (and first-column-width for rotated tables)
  SET @table_width = N'';
  SET @firstcol_width = N'';
  IF @width1 IS NOT NULL SET @table_width = N' width="'+@width1+N'"';
  IF @width2 IS NOT NULL SET @firstcol_width = N' width="'+@width2+N'"';

  -- Set table-color and even-row-color
  SET @table_color = N'';
  SET @evenrow_color = N'';
  IF @bgcolor1 IS NOT NULL SET @table_color = N'background-color: '+@bgcolor1+';';
  IF @bgcolor2 IS NOT NULL AND @rotate = 0 SET @evenrow_color = N'background-color: '+@bgcolor2+';';

  -- Set numalign
  SET @numalign = N'right';
  IF @numleft = 1 SET @numalign = N'left';

  -- Check table (or view) exists
  -- System function OBJECT_ID requires '[tempdb]..' notation for temp tables.
  IF OBJECT_ID(REPLACE(@table_name,'[#','[tempdb]..[#'),'U') IS NULL AND OBJECT_ID(@table_name,'V') IS NULL
  BEGIN
    SET @message = 'ERROR - Table or view does not exist: '+@table_name
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Describe table
  SET @query = N'SELECT * FROM ' + @table_name;
  INSERT INTO @table_fields ([column_ordinal], [name], [system_type_id], [error_number], [orderfield], [redfield], [boldfield], [italicfield], [urlfield], [skipfield])
  SELECT [column_ordinal], [name], [system_type_id], [error_number], 0, 0, 0, 0, 0, 0
  FROM sys.dm_exec_describe_first_result_set(@query, NULL, 0);

  IF EXISTS (SELECT '' FROM @table_fields WHERE [error_number] IS NOT NULL)
  BEGIN
    SET @message = 'ERROR - Description failed for table: ' + @table_name;
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF @whereclause IS NOT NULL
  BEGIN
    IF @whereclause NOT LIKE N'WHERE %'
    BEGIN
      SET @message = 'ERROR - Whereclause does not start with WHERE for table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
    SET @query = 'SET @p1 = (SELECT COUNT(*) FROM '+ @table_name + ' ' + @whereclause+')';
    BEGIN TRY
      EXEC sp_executesql @stmt=@query, @params=N'@p1 INT OUTPUT', @p1=@rec_cnt OUTPUT;
    END TRY
    BEGIN CATCH
      SET @message = 'ERROR - Whereclause has syntax error for table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END CATCH
  END

  IF @orderfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](REPLACE(REPLACE(@orderfields,' ASC',''),' DESC',''),',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields))
    BEGIN
      SET @message = 'ERROR - Orderfields has non-existing field in table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @redfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](@redfields,',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields))
    BEGIN
      SET @message = 'ERROR - Redfields has non-existing field in table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @boldfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](@boldfields,',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields))
    BEGIN
      SET @message = 'ERROR - Boldfields has non-existing field in table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @italicfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](@italicfields,',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields))
    BEGIN
      SET @message = 'ERROR - Italicfields has non-existing field in table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @urlfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](@urlfields,',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields))
    BEGIN
      SET @message = 'ERROR - Urlfields has non-existing field in table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @skipfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](@skipfields,',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields))
    BEGIN
      SET @message = 'ERROR - Skipfields has non-existing field in table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @urlfields IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [dbo].[fun_nsplit](@urlfields,',') WHERE [value] NOT IN (SELECT [name] FROM @table_fields WHERE [system_type_id] IN (167, 231)))
    BEGIN
      SET @message = 'ERROR - Urlfields has field with incompatible type for table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @urlimages IS NOT NULL
  BEGIN
    IF (SELECT COUNT(*) FROM [dbo].[fun_nsplit](@urlimages,',')) <> (SELECT COUNT(*) FROM [dbo].[fun_nsplit](@urlfields,','))
    BEGIN
      SET @message = 'ERROR - Urlimages has wrong number of elements for table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF @urlstrings IS NOT NULL
  BEGIN
    IF (SELECT COUNT(*) FROM [dbo].[fun_nsplit](@urlstrings,',')) <> (SELECT COUNT(*) FROM [dbo].[fun_nsplit](@urlfields,','))
    BEGIN
      SET @message = 'ERROR - Urlstrings has wrong number of elements for table: ' + @table_name;
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  UPDATE t1
  SET t1.[orderfield] = t2.[position],
      t1.[sortorder] = CASE WHEN t2.[value] LIKE N'% DESC' THEN N'DESC' ELSE N'ASC' END 
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@orderfields,',') t2 ON REPLACE(REPLACE(t2.[value],' ASC',''),' DESC','') = t1.[name];

  UPDATE t1
  SET t1.[redfield] = t2.[position]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@redfields,',') t2 ON  t2.[value] = t1.[name];

  UPDATE t1
  SET t1.[boldfield] = t2.[position]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@boldfields,',') t2 ON  t2.[value] = t1.[name];

  UPDATE t1
  SET t1.[italicfield] = t2.[position]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@italicfields,',') t2 ON t2.[value] = t1.[name];

  UPDATE t1
  SET t1.[urlfield] = t2.[position]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@urlfields,',') t2 ON t2.[value] = t1.[name];
  
  UPDATE t1
  SET t1.[skipfield] = t2.[position]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@skipfields,',') t2 ON t2.[value] = t1.[name];

  UPDATE t1
  SET t1.[urlimage] = t2.[value]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@urlimages,',') t2 ON t2.[position] = t1.[urlfield];

  UPDATE t1
  SET t1.[urlstring] = t2.[value]
  FROM @table_fields t1
  INNER JOIN [dbo].[fun_nsplit](@urlstrings,',') t2 ON t2.[position] = t1.[urlfield];

  -- Define style
  SET @style = N'<style>'+ @crlf +
  N'table.'+@table_class+N' {' + @table_color + ' border: 1px solid DarkGray; border-collapse: collapse;}' + @crlf +
  N'.'+@table_class+N' th, .'+@table_class+N' td {border: 1px solid DarkGray; padding: 3px; vertical-align:top;}' + @crlf;
  IF @evenrow_color <> N'' SET @style = @style + N'.'+@table_class+N' tr:nth-child(even) {'+@evenrow_color+'}' + @crlf;
  SET @style = @style + N'</style>' + @crlf;

  -- Define table
  SET @table = N'<table' + @table_width + N' class="' + @table_class;
  IF @sortable = 1
  BEGIN
    SET @table = @table + N' table-sort';
  END
  ELSE
  BEGIN
    IF @sortable = 2
    BEGIN
      SET @table = @table + N' table-sort table-arrows';
    END
  END
  SET @table = @table + N'">' + @crlf;

  IF @rotate = 0
  BEGIN
    -- Define thead
    SET @columnlist1 = (SELECT STRING_AGG(CONVERT(NVARCHAR(MAX),N'<th>' + REPLACE(dbo.fun_text2html([name]),'_',' ') + N'</th>'),N'') WITHIN GROUP (ORDER BY [column_ordinal])
    FROM @table_fields
    WHERE [skipfield] = 0);
  
    SET @thead = N'<thead>' + @crlf + N'<tr style="background-color:LightGray;">' + @columnlist1 + N'</tr>' + @crlf + N'</thead>' + @crlf;
  
    -- Define select statement
    SET @columnlist2 = (SELECT STRING_AGG(CONVERT(NVARCHAR(MAX),'[td/@style]=''' + 
      CASE
        WHEN [system_type_id] IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) -- tinyint, smallint, int, real, money, float, bit, decimal, numeric, smallmoney, bigint
          THEN N'text-align:'+@numalign+';'
        WHEN [system_type_id]  IN (34, 98, 240, 165, 173, 189, 241) -- image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp, xml
          THEN N'text-align:left;color:Gray;'
          ELSE N'text-align:left;'
      END +
      CASE
        WHEN [redfield] > 0
          THEN N'color:Red;'
          ELSE N''
      END +
      CASE
        WHEN [boldfield] > 0
          THEN N'font-weight:bold;'
          ELSE N''
      END +
      CASE
        WHEN [italicfield] > 0
          THEN N'font-style:italic;'
          ELSE N''
      END +
      ''',' + 
      CASE
        WHEN [system_type_id] IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) -- tinyint, smallint, int, real, money, float, bit, decimal, numeric, smallmoney, bigint
          THEN '[td] = ISNULL(CONVERT(VARCHAR,[' + [name] + ']),''''), '''''
        WHEN [system_type_id] = 40 -- date -> yyyy-mm-dd
          THEN '[td] = ISNULL(CONVERT(NVARCHAR(10),[' + [name] + '],120),''''), '''''
        WHEN [system_type_id] = 41 -- time -> hh:mi:ss:mmm(24h)
          THEN '[td] = ISNULL(CONVERT(NVARCHAR,[' + [name] + '],114),''''), '''''
        WHEN [system_type_id] IN (42, 43) -- datetimeoffset, datetime2 -> yyyy-mm-dd hh:mi:ss:mmm(24h)
          THEN '[td] = ISNULL(CONVERT(NVARCHAR,[' + [name] + '],121),''''), '''''
        WHEN [system_type_id] IN (58, 61) -- smalldatetime, datetime -> yyyy-mm-dd hh:mi:ss(24h)
          THEN '[td] = ISNULL(CONVERT(NVARCHAR,[' + [name] + '],120),''''), '''''
        WHEN [system_type_id] = 36 -- uniqueidentifier
          THEN '[td] = ISNULL(CONVERT(NVARCHAR(36),[' + [name] + ']),''''), '''''
        WHEN [system_type_id] IN (35, 175, 99, 239) -- text, char, ntext, nchar
          THEN '[td] = ISNULL([' + [name] + '],''''), '''''
        WHEN [system_type_id] IN (167, 231) -- varchar, nvarchar/sysname
          THEN CASE
                 WHEN [urlfield] > 0
                   THEN CASE
                          WHEN ISNULL([urlimage],'') <> ''
                            THEN '[td] = ISNULL('''+@tag1+'''+['+[name] + ']+'''+@tag2+'''+'''+@tag3+'''+''' + [urlimage] + '''+'''+@tag2+'''+'''+@tag4+''',''''), '''''
                          WHEN ISNULL([urlstring],'') <> ''
                            THEN '[td] = ISNULL('''+@tag1+'''+['+[name] + ']+'''+@tag2+'''+''' + [urlstring] + '''+'''+@tag4+''',''''), '''''
                            ELSE '[td] = ISNULL('''+@tag1+'''+['+[name] + ']+'''+@tag2+'''+[' + [name] + ']+'''+@tag4+''',''''), '''''
                        END
                   ELSE '[td] = ISNULL([' + [name] + '],''''), '''''
               END
        WHEN [system_type_id]  IN (34, 98, 240, 165, 173, 189, 241) -- image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp, xml
          THEN '[td] = ''<Binary data>'', '''''
          ELSE '[td] = ''<Unsupported datatype>'', '''''
      END),',') WITHIN GROUP (ORDER BY [column_ordinal])
    FROM @table_fields
    WHERE [skipfield] = 0);
  
    SET @select = N'SELECT '+ @columnlist2 + ' FROM ' + @table_name;
    IF @whereclause IS NOT NULL SET @select = @select + ' ' + @whereclause;
    IF @orderfields IS NOT NULL SET @select = @select + N' ORDER BY ' + (SELECT STRING_AGG(CONVERT(NVARCHAR(MAX),'['+[name]+'] '+[sortorder]),',') WITHIN GROUP (ORDER BY [orderfield]) FROM @table_fields WHERE [orderfield] > 0);
 
    -- Execute select statement for XML
    SET @sqlcmd = N'SET @p1 = CONVERT(NVARCHAR(MAX),('+ @select + N' FOR XML PATH (''tr''), TYPE))';
    EXEC sp_executesql @stmt=@sqlcmd, @params=N'@p1 NVARCHAR(MAX) OUTPUT', @p1=@tbody OUTPUT;

    -- Add CrLf after each table row
    SET @tbody = REPLACE(@tbody,'</tr>','</tr>' + @crlf);
  END
  ELSE
  BEGIN
    -- Rotated output
    SET @thead = N'' -- No thead for rotated output

    DECLARE cur_table2html CURSOR READ_ONLY FOR
      SELECT [column_ordinal], [name]
      FROM @table_fields
      WHERE [skipfield] = 0
      ORDER BY [column_ordinal];

    OPEN cur_table2html
    WHILE 1 < 2
    BEGIN
      FETCH NEXT FROM cur_table2html INTO @colno, @colname
      IF @@FETCH_STATUS <> 0 BREAK;

      SET @row = N'';

      -- Define select statement
      SET @columnlist2 = (SELECT STRING_AGG(CONVERT(NVARCHAR(MAX),'[td/@style]=''' + 
        CASE
          WHEN [system_type_id] IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) -- tinyint, smallint, int, real, money, float, bit, decimal, numeric, smallmoney, bigint
            THEN N'text-align:'+@numalign+';'
          WHEN [system_type_id]  IN (34, 98, 240, 165, 173, 189, 241) -- image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp, xml
            THEN N'text-align:left;color:Gray;'
            ELSE N'text-align:left;'
        END +
        CASE
          WHEN [redfield] > 0
            THEN N'color:Red;'
            ELSE N''
        END +
        CASE
          WHEN [boldfield] > 0
            THEN N'font-weight:bold;'
            ELSE N''
        END +
        CASE
          WHEN [italicfield] > 0
            THEN N'font-style:italic;'
            ELSE N''
        END +
        ''',' + 
        CASE
          WHEN [system_type_id] IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) -- tinyint, smallint, int, real, money, float, bit, decimal, numeric, smallmoney, bigint
            THEN '[td] = ISNULL(CONVERT(VARCHAR,[' + [name] + ']),''''), '''''
          WHEN [system_type_id] = 40 -- date -> yyyy-mm-dd
            THEN '[td] = ISNULL(CONVERT(NVARCHAR(10),[' + [name] + '],120),''''), '''''
          WHEN [system_type_id] = 41 -- time -> hh:mi:ss:mmm(24h)
            THEN '[td] = ISNULL(CONVERT(NVARCHAR,[' + [name] + '],114),''''), '''''
          WHEN [system_type_id] IN (42, 43) -- datetimeoffset, datetime2 -> yyyy-mm-dd hh:mi:ss:mmm(24h)
            THEN '[td] = ISNULL(CONVERT(NVARCHAR,[' + [name] + '],121),''''), '''''
          WHEN [system_type_id] IN (58, 61) -- smalldatetime, datetime -> yyyy-mm-dd hh:mi:ss(24h)
            THEN '[td] = ISNULL(CONVERT(NVARCHAR,[' + [name] + '],120),''''), '''''
          WHEN [system_type_id] = 36 -- uniqueidentifier
            THEN '[td] = ISNULL(CONVERT(NVARCHAR(36),[' + [name] + ']),''''), '''''
          WHEN [system_type_id] IN (35, 175, 99, 239) -- text, char, ntext, nchar
            THEN '[td] = ISNULL([' + [name] + '],''''), '''''
          WHEN [system_type_id] IN (167, 231) -- varchar, nvarchar/sysname
            THEN CASE
                   WHEN [urlfield] > 0
                     THEN CASE
                            WHEN ISNULL([urlimage],'') <> ''
                              THEN '[td] = ISNULL('''+@tag1+'''+['+[name] + ']+'''+@tag2+'''+'''+@tag3+'''+''' + [urlimage] + '''+'''+@tag2+'''+'''+@tag4+''',''''), '''''
                            WHEN ISNULL([urlstring],'') <> ''
                              THEN '[td] = ISNULL('''+@tag1+'''+['+[name] + ']+'''+@tag2+'''+''' + [urlstring] + '''+'''+@tag4+''',''''), '''''
                              ELSE '[td] = ISNULL('''+@tag1+'''+['+[name] + ']+'''+@tag2+'''+[' + [name] + ']+'''+@tag4+''',''''), '''''
                          END
                     ELSE '[td] = ISNULL([' + [name] + '],''''), '''''
                 END
          WHEN [system_type_id]  IN (34, 98, 240, 165, 173, 189, 241) -- image, sql_variant, hierarchyid/geometry/geography, varbinary, binary, timestamp, xml
            THEN '[td] = ''<Binary data>'', '''''
            ELSE '[td] = ''<Unknown datatype>'', '''''
        END),',') WITHIN GROUP (ORDER BY [column_ordinal])
      FROM @table_fields
      WHERE [column_ordinal] = @colno);

      SET @select = N'SELECT '+ @columnlist2 + ' FROM ' + @table_name;
      IF @whereclause IS NOT NULL SET @select = @select + ' ' + @whereclause;
      IF @orderfields IS NOT NULL SET @select = @select + N' ORDER BY ' + (SELECT STRING_AGG(CONVERT(NVARCHAR(MAX),'['+[name]+'] '+[sortorder]),',') WITHIN GROUP (ORDER BY [orderfield]) FROM @table_fields WHERE [orderfield] > 0);
    
      -- Execute select statement for XML (without tr)
      SET @sqlcmd = N'SET @p1 = CONVERT(NVARCHAR(MAX),('+ @select + N' FOR XML PATH (''''), TYPE))';
      EXEC sp_executesql @stmt=@sqlcmd, @params=N'@p1 NVARCHAR(MAX) OUTPUT', @p1=@row OUTPUT;

      SET @tbody = @tbody + N'<tr><td'+@firstcol_width+N' style="text-align:left;font-weight:bold;background-color:LightGray;">'+@colname+N'</td>' + @row + N'</tr>' + @crlf;
    END
    CLOSE cur_table2html
    DEALLOCATE cur_table2html
  END

  -- Replace markers
  SET @tbody = REPLACE(@tbody,@tag1,N'<a href="')
  SET @tbody = REPLACE(@tbody,@tag2,N'">')
  SET @tbody = REPLACE(@tbody,@tag3,N'<img src="')
  SET @tbody = REPLACE(@tbody,@tag4,N'</a>')

  -- Replace XML coded CrLf
  SET @tbody = REPLACE(@tbody,N'&#x0D;',N'<br>');

  -- Concatenate style, table, thead and tbody
  -- If no rows selected, tbody is NULL, and Html (returnvalue) is NULL.
  SET @html = @style + @table + @thead + N'<tbody>' +@crlf + @tbody +  N'</tbody>' + @crlf + N'</table>' + @crlf;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_check_database_backup       Script Date: 2026-05-15 19:31:09.370 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_check_database_backup]')) DROP PROCEDURE [dbo].[prc_check_database_backup];
GO

CREATE PROCEDURE [dbo].[prc_check_database_backup] (
  @log_backup_interval_minutes INT = 60,
  @system_backup_interval_hours INT = 4,
  @error_report_path VARCHAR(255) = '',
  @email_address NVARCHAR(100) = '') AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Check database backup state.

Check if the last backup of all ONLINE databases are not too old:
-	The last weekly scheduled Full backup of user databases is max 180 hours old.
-	The last daily scheduled Diff backup of user databases is max 36 hours old. Als also a recent full backup fulfils.
-	The last transaction log backup of user databases scheduled with @log_backup_interval_minutes, is max 1.5 
  times @log_backup_interval_minutes minutes old. Also a recent Full- or Diff backup fulfils.
-	The last full backup of system databases scheduled with @systemdb_max_hours, is max 1.5
  times @systemdb_max_hours hours old.

Other checks:
- Check if readonly databases are in simple recovery model. Transaction log backups of readonly database get the 
  COPY_ONLY flag and are not in the backupchain and cannot be used for recover actions.
- Check if all databases have a "start backup", i.e. begin of backup chain.

List the database backup state (in de output grid).

On errors:
 - Send the Error report to email_adddress if @email_address is given.
 - Save the Error report to text file if @error_report_path is given.

PARAMETERS
 - 1 @log_backup_interval_minutes  INT            Job schedule interval in minutes for the user databases transaction log backup, 15 / 30 / 60 (default).
 - 2 @system_backup_interval_hours INT            Job schedule interval in hours for the system databases backup, 1 / 2 / 4 (default) / 6 / 8 / 12 / 24.
 - 3 @error_report_path            VARCHAR(255)   Path to deliver the error report file.
 - 4 @email_address                NVARCHAR(100)  Email address to deliver error report mail.

EXAMPLES
-- Check database backup state using default backup intervals.
EXEC [dbo].[prc_check_database_backup] @error_report_path='C:\Data\MSSQL\error_report', @email_address='g.mantel@gmail.com';

USED OBJECTS
 - dbo.fun_sqlversion             (UDF in local database)
 - dbo.fun_greatest_date2         (UDF in local database)
 - dbo.fun_greatest_date3         (UDF in local database)
 - dbo.fun_FolderExist            (UDF in local database)
 - dbo.prc_table2html             (SP in local database)
 - dbo.prc_save_text_to_utf8file  (SP in local database)
 - msdb.dbo.sp_send_dbmail

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

HISTORY
2026-05-12 - Created procedure
2026-05-13 - Always select the database backup state list to the output grid.
           - Added field Last_backup_type
           - The @log_backup_interval_minutes minimum is now 15 minutes
2026-05-14 - Changed error_messages

TAGS
<program>
  <description>Check database backup state</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2026-05-12</created>
  <lastmodified>2026-05-14</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @message VARCHAR(512)
  DECLARE @version NUMERIC(5,1)
  DECLARE @ishadrenabled INT
  DECLARE @txt NVARCHAR(MAX) = N''
  DECLARE @html NVARCHAR(MAX) = N''
  DECLARE @subject VARCHAR(255)
  DECLARE @crlf VARCHAR(2)
  DECLARE @error_cnt INT
  DECLARE @time VARCHAR(19)
  DECLARE @error_report_file VARCHAR(255)

  DECLARE @systemdb_max_hours INT
  DECLARE @userdb_full_max_hours INT
  DECLARE @userdb_diff_max_hours INT
  DECLARE @userdb_log_max_minutes INT

  SET @message = 'Executing prc_check_database_backup ...';
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  -- Get SQL version
  SET @version = [dbo].[fun_sqlversion]();

  -- Compliant SQL versions are between SQL2014 and SQL2022
  IF @version NOT IN (12.0, 13.0, 14.0, 15.0, 16.0)
  BEGIN
    SET @message = 'ERROR - SQL Server version not supported';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  IF ISNULL(@log_backup_interval_minutes,-1) NOT IN (15, 30, 60)
  BEGIN
    SET @message = 'ERROR - Parameter @log_backup_interval_minutes must be 15, 30 or 60';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@system_backup_interval_hours,-1) NOT IN (1, 2, 3, 4, 6, 8, 12, 24)
  BEGIN
    SET @message = 'ERROR - Parameter @system_backup_interval_hours must be 1, 2, 3, 4, 6, 8, 12 or 24';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Set fixed intervals for full- and diff backups
  SET @userdb_full_max_hours = 180
  SET @userdb_diff_max_hours = 36;

  -- Set variable intervals for log- and systemdb backups
  SET @userdb_log_max_minutes = CEILING(@log_backup_interval_minutes * 1.5);
  SET @systemdb_max_hours = CEILING(@system_backup_interval_hours * 1.5);

  -- Get IsHadrEnabled
  SET @ishadrenabled = ISNULL(CONVERT(INT,SERVERPROPERTY('IsHadrEnabled')),0);

  IF (SELECT OBJECT_ID('tempdb..#temp_status_databases')) IS NOT NULL
    DROP TABLE #temp_status_databases;

  CREATE TABLE #temp_status_databases (
	  [database_name] NVARCHAR(128) NOT NULL,
    [state_desc] NVARCHAR(60),
	  [recovery_model_desc] NVARCHAR(60),
    [is_read_only] BIT,
	  [is_in_standby] BIT,
	  [need_full_backup] BIT,
	  [last_full_backup_date] DATETIME,
	  [last_diff_backup_date] DATETIME,
	  [last_log_backup_date] DATETIME,
    [last_backup_type] VARCHAR(4),
    [is_ls_primary_db] BIT,
    [is_ls_secondary_db] BIT,
    [is_hadr_db] BIT,
    [hadr_is_primary_replica] BIT,
    [hadr_is_backup_preferred_replica] BIT,
    [error_message] VARCHAR(255),
  CONSTRAINT [pk_temp_status_databases] PRIMARY KEY CLUSTERED ([database_name] ASC));

  -- Get databases
  -- If last_log_backup_lsn = NULL then the recovery model is changed from SIMPLE to FULL or BULK_LOGGED.
  -- If differential_base_lsn = NULL then database is newly created
  INSERT INTO #temp_status_databases (
    [database_name],
    [state_desc],
    [recovery_model_desc],
    [is_read_only],
    [is_in_standby],
    [need_full_backup], 
    [last_full_backup_date],
    [last_diff_backup_date],
    [last_log_backup_date],
    [last_backup_type],
    [is_ls_primary_db],
    [is_ls_secondary_db],
    [is_hadr_db])
 SELECT 
    t1.[name] AS [database_name],
    t1.[state_desc],
    t1.[recovery_model_desc],
    t1.[is_read_only],
    t1.[is_in_standby],
    CASE WHEN (((t1.[recovery_model_desc] <> 'SIMPLE' AND t2.[last_log_backup_lsn] = 0) OR t3.[differential_base_lsn] = 0) AND t1.[name] <> 'tempdb') THEN 1 ELSE 0 END AS [need_full_backup],
    t4.[last_full_backup_date],
    t5.[last_diff_backup_date],
    t6.[last_log_backup_date],
    CASE t7.[last_backup_type] WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' WHEN 'L' THEN 'LOG' END AS [last_backup_type],
    CASE t9.[cnt] WHEN 0 THEN 0 ELSE 1 END AS [is_ls_primary_db],
    CASE t10.[cnt] WHEN 0 THEN 0 ELSE 1 END AS [is_ls_secondary_db],
    0 AS [is_hadr_db]
  FROM master.sys.[databases] t1
  OUTER APPLY (
    SELECT ISNULL([last_log_backup_lsn],0) AS [last_log_backup_lsn]
    FROM master.sys.[database_recovery_status]
    WHERE [database_id] = t1.[database_id]) t2
  OUTER APPLY (
    SELECT MAX(ISNULL([differential_base_lsn],0)) AS [differential_base_lsn]
    FROM master.sys.[master_files] t2
    WHERE [database_id] = t1.[database_id]
    AND [type] = 0) t3
  OUTER APPLY (
    SELECT TOP 1
      t41.[backup_start_date] AS [last_full_backup_date]
    FROM msdb.dbo.[backupset] t41
    WHERE t41.[database_name] = t1.[name]  
    AND t41.[server_name] = @@SERVERNAME
    AND t41.[type] = 'D'
    AND t41.[is_copy_only] = 0
    ORDER BY t41.[backup_finish_date] DESC) t4
  OUTER APPLY (
    SELECT TOP 1
      t51.[backup_start_date] AS [last_diff_backup_date]
    FROM msdb.dbo.[backupset] t51
    WHERE t51.[database_name] = t1.[name]
    AND t51.[server_name] = @@SERVERNAME
    AND t51.[type] = 'I'
    AND t51.[is_copy_only] = 0
    ORDER BY t51.[backup_finish_date] DESC) t5
  OUTER APPLY (
    SELECT TOP 1
      t61.[backup_start_date] AS [last_log_backup_date]
    FROM msdb.dbo.[backupset] t61
    WHERE t61.[database_name] = t1.[name]
    AND t61.[server_name] = @@SERVERNAME
    AND t61.[type] = 'L'
    AND t61.[is_copy_only] = 0
    ORDER BY t61.[backup_finish_date] DESC) t6
  OUTER APPLY (
    SELECT TOP 1
      t71.[type] AS [last_backup_type]
    FROM msdb.dbo.[backupset] t71
    WHERE t71.[database_name] = t1.[name]
    AND t71.[server_name] = @@SERVERNAME
    AND t71.[is_copy_only] = 0
    ORDER BY t71.[backup_finish_date] DESC) t7
  OUTER APPLY (
    SELECT COUNT(*) AS [cnt]
    FROM msdb.dbo.[log_shipping_primary_databases]
    WHERE [primary_database] = t1.[name]) t9
  OUTER APPLY (
    SELECT COUNT(*) AS [cnt]
    FROM msdb.dbo.[log_shipping_secondary_databases]
    WHERE [secondary_database] = t1.[name]) t10
  ORDER BY t1.[name];

  -- Update #temp_status_databases table with HADR related properties
  -- Only support for HADR on sql2014 and higher versions (because sys.fn_hadr_is_primary_replica function is not available on sql2012)
  IF @ishadrenabled = 1
  BEGIN
    UPDATE t1
    SET t1.[is_hadr_db] = 1,
        t1.[hadr_is_primary_replica] = sys.fn_hadr_is_primary_replica(t1.[database_name]),
        t1.[hadr_is_backup_preferred_replica] = sys.fn_hadr_backup_is_preferred_replica(t1.[database_name])
    FROM #temp_status_databases t1
    INNER JOIN master.sys.[availability_databases_cluster] t2 ON t2.[database_name] COLLATE Latin1_General_BIN = t1.[database_name] COLLATE Latin1_General_BIN;
  END

  -- Check if READONLY databases are in SIMPLE recovery model.
  -- Log backups of READONLY databases get a COPY_ONLY flag and are not in the backupchain. Best practice is to set READONLY databases in SIMPLE recovery model.
  UPDATE t1
  SET t1.[error_message] = 'Adjust recovery_model of READONLY database to SIMPLE.'
  FROM #temp_status_databases t1
  WHERE t1.[database_name] NOT IN ('master', 'msdb', 'tempdb')
  AND t1.[is_ls_secondary_db] = 0
  AND t1.[is_in_standby] = 0
  AND t1.[is_hadr_db] = 0
  AND t1.[is_read_only] = 1
  AND t1.[recovery_model_desc] <> 'SIMPLE'
  AND ISNULL(t1.[state_desc],'') = 'ONLINE';

  -- Check if user- or system database needs a "start backup" (=full).
  -- Skip LS secondary-, Standby-  and HADR databases. HADR databases will be checked separately.
  UPDATE t1
  SET t1.[error_message] = 'Database needs a start backup.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[database_name] <> 'tempdb'
  AND t1.[is_ls_secondary_db] = 0
  AND t1.[is_in_standby] = 0
  AND t1.[is_hadr_db] = 0
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND t1.[need_full_backup] = 1;

  -- Check if the last full backup of system databases is too old.
  UPDATE t1
  SET t1.[error_message] = 'System database last full backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[database_name] IN ('master', 'msdb', 'model')
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND ISNULL(t1.[last_full_backup_date],CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@systemdb_max_hours, GETDATE());

  -- Check if the last full backup of user databases is too old.
  -- Skip LS secondary-, Standby-  and HADR databases. HADR databases will be checked separately.
  UPDATE t1
  SET t1.[error_message] = 'User database last full backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[database_name] NOT IN ('master', 'msdb', 'model', 'tempdb')
  AND t1.[is_ls_secondary_db] = 0
  AND t1.[is_in_standby] = 0
  AND t1.[is_hadr_db] = 0
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND ISNULL(t1.[last_full_backup_date],CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@userdb_full_max_hours, GETDATE());

  -- Check if the last differential backup of user databases is too old.
  -- Also a full backup fulfils.
  -- Skip LS secondary-, Standby-  and HADR databases. HADR databases will be checked separately.
  -- Skip check if a full backup is already needed.
  UPDATE t1
  SET t1.[error_message] = 'User database last differental backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[database_name] NOT IN ('master', 'msdb', 'model', 'tempdb')
  AND t1.[is_ls_secondary_db] = 0
  AND t1.[is_in_standby] = 0
  AND t1.[is_hadr_db] = 0
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND ISNULL([dbo].[fun_greatest_date2](t1.[last_full_backup_date],t1.[last_diff_backup_date]),CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@userdb_diff_max_hours, GETDATE());

  -- Check if the last transactionlog backup of user databases is too old.
  -- Also a full- or differential backup fulfils.
  -- Skip LS secondary-, Standby-  and HADR databases.
  -- Skip check if a full- or diffential backup is already needed.
  -- Skip READONLY databases. Log backups of READONLY databases get a COPY_ONLY flag and are not in the backupchain. This is previously checked.
  -- Skip the model database. However the model database can be in FULL or BULK_LOGGED recovery model, the rythm of system databases is used for de log backup check.
  UPDATE t1
  SET t1.[error_message] = 'User database last transactionlog backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[database_name] NOT IN ('master', 'msdb', 'model', 'tempdb')
  AND t1.[is_ls_secondary_db] = 0
  AND t1.[is_in_standby] = 0
  AND t1.[is_hadr_db] = 0
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND t1.[recovery_model_desc] <> 'SIMPLE'
  AND t1.[is_read_only] = 0
  AND ISNULL([dbo].[fun_greatest_date3](t1.[last_full_backup_date],t1.[last_diff_backup_date],t1.[last_log_backup_date]),CONVERT(DATETIME,'19000101')) < DATEADD(mi,-1*@userdb_log_max_minutes, GETDATE());

  -- Check if the last transactionlog backup of the model database is too old.
  -- Also a full- or differential backup fulfils.
  -- Skip check if a full- or diffential backup is already needed.
  -- Skip READONLY model databases. Log backups of READONLY databases get a COPY_ONLY flag and are not in the backupchain. This is previously checked.
  UPDATE t1
  SET t1.[error_message] = 'Model database last transactionlog backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[database_name] = 'model'
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND t1.[recovery_model_desc] <> 'SIMPLE'
  AND t1.[is_read_only] = 0
  AND ISNULL([dbo].[fun_greatest_date3](t1.[last_full_backup_date],t1.[last_diff_backup_date],t1.[last_log_backup_date]),CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@systemdb_max_hours, GETDATE());

  -- HADR databases are supposed to be full backupped on the primary replica.
  -- Check if the last full backup of the HADR database on the primary replica is too old.
  UPDATE t1
  SET t1.[error_message] = 'HADR primary_replica database last full backup of is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[is_hadr_db] = 1
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND t1.[hadr_is_primary_replica] = 1
  AND ISNULL(t1.[last_full_backup_date],CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@userdb_full_max_hours, GETDATE());

  -- HADR databases are supposed to be differential backupped on the primary replica.
  -- Check if the last differential backup of the HADR database on the primary replica is too old.
  -- Also a full backup fulfils.
  UPDATE t1
  SET t1.[error_message] = 'HADR primary_replica database last differental backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[is_hadr_db] = 1
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND t1.[hadr_is_primary_replica] = 1
  AND ISNULL([dbo].[fun_greatest_date2](t1.[last_full_backup_date],t1.[last_diff_backup_date]),CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@userdb_diff_max_hours, GETDATE());

  -- HADR databases are supposed to be transactionlog backupped on the backup preferred replica.
  -- Check if the last transactionlog backup of the HADR database on the backup preferred replica is too old.
  -- Also a full- or differential backup fulfils.
  UPDATE t1
  SET t1.[error_message] = 'HADR backup_preferred_replica database last transactionlog backup is too old.'
  FROM #temp_status_databases t1
  WHERE t1.[error_message] IS NULL
  AND t1.[is_hadr_db] = 1
  AND t1.[hadr_is_backup_preferred_replica] = 1
  AND ISNULL(t1.[state_desc],'') = 'ONLINE'
  AND t1.[recovery_model_desc] <> 'SIMPLE'
  AND ISNULL([dbo].[fun_greatest_date3](t1.[last_full_backup_date],t1.[last_diff_backup_date],t1.[last_log_backup_date]),CONVERT(DATETIME,'19000101')) < DATEADD(hh,-1*@systemdb_max_hours, GETDATE());

  -- List database backup status to the output grid.
  SELECT *
  FROM #temp_status_databases
  ORDER BY [database_name];

  -- Generate Error report
  SET @error_cnt = (SELECT COUNT(*) FROM #temp_status_databases WHERE [error_message] IS NOT NULL)
  IF @error_cnt > 0
  BEGIN
    SET @crlf = CHAR(13)+CHAR(10);
    SET @time = CONVERT(VARCHAR,GETDATE(),120)
    SET @subject = 'Backup check error report for SQL Server '+@@SERVERNAME+ ' ('+CONVERT(VARCHAR,@error_cnt)+' error'+CASE @error_cnt WHEN 1 THEN '' ELSE 's' END+')';
    SET @txt = '<h4>'+@subject + ' generated on '+@time+'</h4>';

    EXEC dbo.prc_table2html
      @table_name = '#temp_status_databases',
      @orderfields = 'database_name',
      @redfields = 'error_message',
      @bgcolor = 'LightCyan, LightBlue',
      @html = @html OUTPUT;
  
    SET @txt = @txt + ISNULL(@html,'') + @crlf;

    IF ISNULL(@error_report_path,'') <> ''
    BEGIN
      -- Strip '\' from dump_path
      IF RIGHT(@error_report_path,1) = '\' SET @error_report_path = LEFT(@error_report_path,LEN(@error_report_path)-1);
      IF dbo.fun_FolderExist(@error_report_path) = 1
      BEGIN
        SET @error_report_file = @error_report_path + '\' + REPLACE(REPLACE(REPLACE(@time,':',''),'-',''),' ','_') + '_backup_check_error_report.html';
        EXEC [dbo].[prc_save_text_to_utf8file] @text=@txt, @filename=@error_report_file;
      END
    END

    IF ISNULL(@email_address,'') <> ''
    BEGIN
      EXEC msdb.dbo.sp_send_dbmail
        @recipients=@email_address,
        @subject=@subject,
        @body=@txt,
        @body_format='HTML';
    END
  END

  DROP TABLE #temp_status_databases;
END
GO