query execute

Try this:
DECLARE @sqlcmd VARCHAR(MAX);
DECLARE @results TABLE (SchemaName NVARCHAR(128), Table_Name NVARCHAR(128), Column_Name NVARCHAR(128), Column_Position INT, DataType VARCHAR(128), max_length INT, AvgLength INT)

SELECT @sqlcmd = STRING_AGG(sqlcmd, CHAR(10))
FROM (
    SELECT sqlcmd = 
        'SELECT 
            ''' + c.TABLE_SCHEMA +''' as SchemaName, 
            ''' + c.TABLE_NAME + ''' as Table_Name, 
            ''' + c.COLUMN_NAME + ''' as Column_Name, 
            ''' + CAST(c.ORDINAL_POSITION  AS VARCHAR(10)) + ''' AS Column_Position, 
            ''' + c.DATA_TYPE + ''' AS DataType, 
            ''' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ''' as max_length
            , CAST((SELECT AVG(LEN(' + QUOTENAME(c.COLUMN_NAME) + ')) FROM ' + QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) + ') AS VARCHAR(10)) AS AvgLength'

    FROM INFORMATION_SCHEMA.COLUMNS c
        INNER JOIN INFORMATION_SCHEMA.TABLES t
        ON t.TABLE_CATALOG = c.TABLE_CATALOG
        AND t.TABLE_NAME = c.TABLE_NAME
        AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_TYPE = 'BASE TABLE'


    WHERE c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar' )
    AND c.CHARACTER_MAXIMUM_LENGTH > 50
    AND c.TABLE_SCHEMA = 'dbo'
) c



INSERT INTO @results
    EXEC (@sqlcmd);

SELECT *
FROM @results r
ORDER BY r.SchemaName, r.Table_Name, r.Column_Position

On SQL 2016 and lower you can use FOR XML PATH which is quite a bit of mumbo-jumbo but it works.

DECLARE @sql nvarchar(MAX)

; WITH CTE AS (
select d.*,
('Select avg(len(' + d.Column_Name + ')) FROM ' + d.SchemaName + '.' + + d.[Table_Name] ) AS cmd
from(
SELECT schema_name(t.schema_id) as SchemaName, t.name as [Table_Name],
c.name as Column_Name,
c.column_id as Column_Position,
type_name(user_type_id) as DataType,
max_length
FROM sys.columns c
join sys.tables t
on t.object_id = c.object_id
WHERE type_name(c.user_type_id) in ('text', 'ntext',
'varchar', 'nvarchar', 'char', 'nchar' )
and t.schema_id=1
and c.max_length>50
) as d
)
SELECT @sql = 
   (SELECT cmd + char(13) + char(10)
    FROM   CTE
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

PRINT  @sql

Note that the output may appear as truncated. This is because the PRINT statement only displays 8000 bytes.

What is your SQL Server version? Perhaps you want to generate the whole query as a variable first and then execute it, not embed dynamic query into the static query.

Thanks for the prompt reply.

I'm using SQL 2014. I' tried it in a var but I can't seem to get it to work ...

DECLARE @vSQL varchar(Max)

set @vSQL =
'select d.*

,' + (Select avg(len(+ ' d.Column_Name ' + )) FROM + ' d.SchemaName ' + '.' + ' d.[Table_Name] )

from(
SELECT schema_name(t.schema_id) as SchemaName, t.name as [Table_Name],
c.name as Column_Name,
c.column_id as Column_Position,
type_name(user_type_id) as DataType,
max_length
FROM sys.columns c
join sys.tables t
on t.object_id = c.object_id
WHERE type_name(c.user_type_id) in (''varchar'', ''nvarchar'')
and t.schema_id=5
and c.max_length>50
) as d;'

exec (@vSQL)

Thanks Tom

Unfortunately as I'm using version Microsoft SQL Server 2014 - 12.0.4100.1 (X64) , I'm getting 'STRING_AGG' is not a recognized built-in function name.

this really has me pulling my hair out ...