Best way to get the max length in every column in a table

hello,

i'd like a query that goes through every column in a table and returns the max length of data in them. i know how to do both separately, just not sure how to join them together and show the column name and the max length within next to each other. sometimes when loading data i run into truncation errors going from the raw table to the process table, and rather than go through fiddling with the lengths in access of 100+ columns, it would be far more efficient to see exactly which column the problem lies in to correct it.

thanks in advance,

erik

You're just looking for some way to automatically generate the query, right?

declare @sql varchar(max)
select @sql = @sql + ', max(length(' + column_name + ')) as Max' + column_name
from information_schema.columns
where table_name = 'YourTable'
-- untested, but needed to only target text columns:
-- and type in ('varchar', 'nvarchar' etc...)

select @sql = 'select ' + substring(@sql, 1) /* take out first comma */ + ' from YourTable'
sp_ExecuteSQL @sql