here you go Roger; a project like this has to use the metadata to determine which columns to query, so using a cursor in this case is fine;

the logic is pretty obvious: get all the char-type columns, get their defined size, and find the max length as it exists in the table.

i'm stuffing the data into a global temp table and finally ordering the info back with a final query of the results. watch for MaxActualLength=0, since that's nulls or no rows in the database, which might not be representative of future data.

example results:

TableName ColumnName DefinedLength MaxActualLength

example exampleid 8 0

example exampletext 30 0

Z_Data_Extract_Activity_C06350_1_TXT RAWDATA 8000 779

the query:

select sysobjects.name as TableName,

syscolumns.name as ColumnName,

TYPE_NAME(syscolumns.xtype) AS VariableType,

syscolumns.length AS DefinedLength,

0 as MaxActualLength

into ##tmp

from sysobjects

inner join syscolumns on sysobjects.id=syscolumns.id

where sysobjects.xtype='U'

declare

@isql varchar(2000),

@tbname varchar(64),

@clname varchar(64)

declare c1 cursor for

select TableName,ColumnName from ##tmp where VariableType in('varchar','char','nvarchar','nchar')

open c1

fetch next from c1 into @tbname,@clname

While @@fetch_status <> -1

begin

select @isql = 'UPDATE ##TMP SET MaxActualLength = (SELECT ISNULL(max(DATALENGTH(@clname)),0) FROM @tbname) WHERE TableName =''@tbname'' and ColumnName =''@clname'''

select @isql = replace(@isql,'@tbname',@tbname)

select @isql = replace(@isql,'@clname',@clname)

print @isql

exec(@isql)

fetch next from c1 into @tbname,@clname

end

close c1

deallocate c1

select * from ##tmp

order by TableName,ColumnName