Determine which column is being called. String or binary data will be truncated .: SQL server

Question

I have an application that will be inserted into several tables with one click. these tables have nearly 100 to 150 columns.

It works fine, but someday someone from a column makes a String error or binary data will be truncated. I know the cause of the error, but I want to know which column is the cause of this problem.

Msg 8152, Level 16, State 13, Line 1
String or binary data will be truncated.

Can I authenticate on a SQL server? Since my application is a production side. I can not reproduce the error.

Answer 1

, VARCHAR(MAX) . DATALENGTH, .

 SELECT MAX(DATALENGTH(col1)) AS col1, MAX(DATALENGTH(col2)) AS col2, etc.
 FROM newTable

, () .

Answer 2

MSDN:

declare @table_name varchar(200)

set @table_name = 'Branch'

declare @max_length_default int, @column varchar(50), @max_length int, @max_length_string varchar(10)

create table #temp_table (column_name varchar(50), max_length int, max_length_default int)

declare @column_id int, @max_column_id int

select @column_id = min(b.column_id), @max_column_id = max(b.column_id) from sys.tables a, sys.columns b where a.[name] = @table_name and a.object_id = b.object_id --and b.system_type_id in ( 231, 167 )

-- select @column_id, @max_column_id

declare @command varchar(2000)

while(@column_id <= @max_column_id)
begin
    set @column = null

    select @column = b.name, @max_length_default = b.max_length from sys.tables a, sys.columns b where a.[name] = @table_name and a.object_id = b.object_id --and b.system_type_id in ( 231, 167 ) 
    and b.column_id = @column_id

    --select @column, @max_length_default

    if( @column is not null )
    begin

        set @command = 'insert into #temp_table(column_name, max_length, max_length_default) select ''' + @column + ''', max(len(cast([' + @column + ']as varchar(8000)))),' + cast(@max_length_default as varchar(5)) + ' from ' + @table_name + ' where [' + @column + '] is not null'

         --select @command

         exec(@command)        
    end

    set @column_id = @column_id + 1
end

select * from #temp_table

drop table #temp_table