SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.foo
(
  a int PRIMARY KEY, 
  h varchar(240),
  i varchar(100),
  j varchar(320)
);

INSERT dbo.foo VALUES 
(1,'123','123','20200101 04:00:00'),
(2,'456','456','20200101'),
(3,'789','789','20200101'),
(4,'867','foo','20200101'),
(5,'876','876','20200101'),
(6,'6.54','654','20200101');

DECLARE @table nvarchar(513) = N'dbo.foo';

DECLARE @sql nvarchar(max) = N'SELECT ', @un nvarchar(max) = N'',
  @un_sub nvarchar(max) = N'
  SELECT ColumnName =  MIN([col $c$]), 
  CurrentType = MIN([type $c$]), 
  LongestValue = MAX([len $c$]), 
  [AllNumerics?] = MIN([is_num $c$]), 
  [AllDates?] = MIN([is_date $c$]),
  [AnyContainDecimal] = MAX([has_dec $c$]) FROM x '

SELECT @sql += N'[col ' + name + '] = ''' + name + ''',
   [type ' + name + '] = '''
  + system_type_name + ''',' + QUOTENAME(name)
  + ', [len ' + name + '] = LEN(' + QUOTENAME(name) + '),
  [is_num ' + name + '] = CONVERT(tinyint, ISNUMERIC(' + QUOTENAME(name) + ')),
  [is_date ' + name + '] = CONVERT(tinyint, ISDATE(' + QUOTENAME(name) + ')),
  [has_dec ' + name + '] = CASE WHEN ISNUMERIC(' + QUOTENAME(name) + ') = 1
    AND ' + QUOTENAME(name) + ' LIKE N''%.%'' THEN 1 ELSE 0 END,',
  @un += N'
UNION ALL ' + REPLACE(@un_sub, N'$c$', name)
  
FROM sys.dm_exec_describe_first_result_set('SELECT * FROM ' + @table, NULL, 1)
WHERE system_type_name like '%char%'

SELECT @sql += N'[$garbage$]='''' FROM ' + @table;

SELECT @sql = N';WITH x AS (
' + @sql + N'
) ' + STUFF(@un, 1, 10, '');

SELECT [The Ugly Generated Query Is:] = @sql;

EXEC sys.sp_executesql @sql;

The Ugly Generated Query Is:
;WITH x AS ( SELECT [col h] = 'h', [type h] = 'varchar(240)',[h], [len h] = LEN([h]), [is_num h] = CONVERT(tinyint, ISNUMERIC([h])), [is_date h] = CONVERT(tinyint, ISDATE([h])), [has_dec h] = CASE WHEN ISNUMERIC([h]) = 1 AND [h] LIKE N'%.%' THEN 1 ELSE 0 END,[col i] = 'i', [type i] = 'varchar(100)',[i], [len i] = LEN([i]), [is_num i] = CONVERT(tinyint, ISNUMERIC([i])), [is_date i] = CONVERT(tinyint, ISDATE([i])), [has_dec i] = CASE WHEN ISNUMERIC([i]) = 1 AND [i] LIKE N'%.%' THEN 1 ELSE 0 END,[col j] = 'j', [type j] = 'varchar(320)',[j], [len j] = LEN([j]), [is_num j] = CONVERT(tinyint, ISNUMERIC([j])), [is_date j] = CONVERT(tinyint, ISDATE([j])), [has_dec j] = CASE WHEN ISNUMERIC([j]) = 1 AND [j] LIKE N'%.%' THEN 1 ELSE 0 END,[$garbage$]='' FROM dbo.foo ) SELECT ColumnName = MIN([col h]), CurrentType = MIN([type h]), LongestValue = MAX([len h]), [AllNumerics?] = MIN([is_num h]), [AllDates?] = MIN([is_date h]), [AnyContainDecimal] = MAX([has_dec h]) FROM x UNION ALL SELECT ColumnName = MIN([col i]), CurrentType = MIN([type i]), LongestValue = MAX([len i]), [AllNumerics?] = MIN([is_num i]), [AllDates?] = MIN([is_date i]), [AnyContainDecimal] = MAX([has_dec i]) FROM x UNION ALL SELECT ColumnName = MIN([col j]), CurrentType = MIN([type j]), LongestValue = MAX([len j]), [AllNumerics?] = MIN([is_num j]), [AllDates?] = MIN([is_date j]), [AnyContainDecimal] = MAX([has_dec j]) FROM x

The Ugly Generated Query Is:

;WITH x AS (
SELECT [col h] = 'h',
[type h] = 'varchar(240)',[h], [len h] = LEN([h]),
[is_num h] = CONVERT(tinyint, ISNUMERIC([h])),
[is_date h] = CONVERT(tinyint, ISDATE([h])),
[has_dec h] = CASE WHEN ISNUMERIC([h]) = 1
AND [h] LIKE N'%.%' THEN 1 ELSE 0 END,[col i] = 'i',
[type i] = 'varchar(100)',[i], [len i] = LEN([i]),
[is_num i] = CONVERT(tinyint, ISNUMERIC([i])),
[is_date i] = CONVERT(tinyint, ISDATE([i])),
[has_dec i] = CASE WHEN ISNUMERIC([i]) = 1
AND [i] LIKE N'%.%' THEN 1 ELSE 0 END,[col j] = 'j',
[type j] = 'varchar(320)',[j], [len j] = LEN([j]),
[is_num j] = CONVERT(tinyint, ISNUMERIC([j])),
[is_date j] = CONVERT(tinyint, ISDATE([j])),
[has_dec j] = CASE WHEN ISNUMERIC([j]) = 1
AND [j] LIKE N'%.%' THEN 1 ELSE 0 END,[$garbage$]='' FROM dbo.foo
)
SELECT ColumnName = MIN([col h]),
CurrentType = MIN([type h]),
LongestValue = MAX([len h]),
[AllNumerics?] = MIN([is_num h]),
[AllDates?] = MIN([is_date h]),
[AnyContainDecimal] = MAX([has_dec h]) FROM x
UNION ALL
SELECT ColumnName = MIN([col i]),
CurrentType = MIN([type i]),
LongestValue = MAX([len i]),
[AllNumerics?] = MIN([is_num i]),
[AllDates?] = MIN([is_date i]),
[AnyContainDecimal] = MAX([has_dec i]) FROM x
UNION ALL
SELECT ColumnName = MIN([col j]),
CurrentType = MIN([type j]),
LongestValue = MAX([len j]),
[AllNumerics?] = MIN([is_num j]),
[AllDates?] = MIN([is_date j]),
[AnyContainDecimal] = MAX([has_dec j]) FROM x

ColumnName	CurrentType	LongestValue	AllNumerics?	AllDates?	AnyContainDecimal
h	varchar(240)	4	1	0	1
i	varchar(100)	3	0	0	0
j	varchar(320)	17	0	1	0