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