Thanks Steve
I am not guaranteeing anything because I never allow use of varchar(max) and have no way to test it, BUT
for Varchar(max) types to work, change this statement:
CREATE TABLE #ProfiledColumns(
ColumnName varchar(128) NOT NULL,
SequenceNumber varchar(10),
NullCount int ,
ModalValue varchar(max),
ModalCount int,
ValueCount int,
MinValue varchar(max),
MaxValue varchar(max),
MaxLength smallint
And this statement:
SET @sqlstring=
'INSERT INTO #ProfiledColumns ' +
'SELECT '''+@ColumnName+''',' +
CAST(@SequenceNumber AS varchar(10)) + ' , ' +
'SUM(CASE WHEN ['+@ColumnName+'] IS NULL THEN 1 ELSE 0 END) ,'+
'(SELECT TOP 1 ['+@ColumnName+'] FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
'(SELECT TOP 1 COUNT(*) FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
'COUNT(DISTINCT ['+@ColumnName+']),' +
'CAST(MIN(CAST(['+@ColumnName+'] AS sql_variant)) AS VARCHAR(max)),'+
'CAST(MAX(CAST(['+@ColumnName+'] AS sql_variant)) AS VARCHAR(max)),'+
'MAX(LEN(CAST(['+@ColumnName+'] AS VARCHAR(255)))) '+
'FROM ' + @Cat_Sch_TableName -- build query string