Maximum Column Content Lengths

Josh,

So you are working on loading data into a table from another table and you're getting truncation errors.  To keep the destination table as small as possible for speed, you need to find out which column is causing the truncation error and how much you need to increase the destination columns size.

Let's use the handy SYS schema to help us out...

DECLARE
       @SchemaName SYSNAME = 'MySchema',
       @TableName SYSNAME = 'MyTable',
       @MaxSelectStatement VARCHAR(MAX)

SELECT
   @MaxSelectStatement = COALESCE(@MaxSelectStatement + CHAR(10) + 'UNION ALL' + CHAR(10),'') +
   'select '''
       + ss.name + ''' as SchemaName, '''
       + st.name + ''' as TableName, '''
       + sc.name + ''' as ColumnName, '''
       + sty.name + ''' as ColumnType, '
       + 'MAX(LEN([' + sc.name + '])) as MaxContentLength '
   + 'from '
       + '[' + ss.name + '].[' + st.name + ']'FROM
   sys.tables st
   INNER JOIN
   sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID
   INNER JOIN
   sys.schemas ss ON st.schema_id = ss.schema_id
   INNER JOIN
   sys.types sty ON sc.system_type_id = sty.system_type_idWHERE
   ss.name = @SchemaName AND
   st.name = @TableName

PRINT(@MaxSelectStatement)
EXEC(@MaxSelectStatement)