SQL Server: Get length of all columns of a table

I was trying to insert data from one table to another table through query. There were around 80 columns. But I encountered with below error.

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.

I know data in one of the source column is more than the destination defined column length. To find is the tedious one. I needed a script to check column length of all columns of source table. This script helped me.

DECLARE @TableName As varchar(500), @TableSchema As varchar(500)
DECLARE @SQL NVARCHAR(MAX)
SET @TableName = 'Mytablename'
SET @TableSchema = 'dbo'

SELECT @SQL = STUFF((SELECT
'
UNION ALL
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' +
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(' +
CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext')
THEN 'DATALENGTH(' ELSE 'LEN(' END + QUOTENAME(Column_Name) +
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' +
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) +
' AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = @TableName
AND table_schema = @TableSchema
AND DATA_TYPE like '%char%'
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')
--print @SQL
EXECUTE (@SQL)

The above script uses DATALENGTH which considers spaces as well. Where as LEN function ignores spaces available at the end of the string. LEN function is used in below script.

DECLARE @TableName As varchar(500), @TableSchema As varchar(500)
DECLARE @SQL NVARCHAR(MAX)
SET @TableName = 'Mytablename'
SET @TableSchema = 'dbo'

SELECT @SQL = STUFF((SELECT
'
UNION ALL
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' +
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(LEN(' + QUOTENAME(Column_Name) +
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' +
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) +
'  AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = @TableName
AND table_schema = @TableSchema
AND DATA_TYPE like '%char%'
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')
--print @SQL
Exec (@SQL)