How do I find maximum length of data in each field

  • Thread starter Thread starter deanboy
  • Start date Start date
I need to be able to find the longest record in each field, and its length
and produce a report with the information.
 
You are using the terms 'record' and 'field' backwards.
A record has fields, therefore you would have 'longest field in a record.
If you table had the following fields - Name, Type, and Agerotia you would
use a union query like this first --
SELECT "Name" AS [Field], Len([Name]) AS [Size]
FROM YourTable
UNION ALL SELECT "Type" AS [Field], Len([Type]) AS [Size]
FROM YourTable
UNION ALL SELECT "Agerotia" AS [Field], Len([Agerotia]) AS [Size]
FROM YourTable;

Run it and then click on the Z-A sort.
 
select sc.name, object_name(sc.id) as tblName, sc.length, sc.type
from syscolumns sc
where id in (select id from sysobjects where xtype = 'u')
 
select 'select ' + char(39) + sc.name + char(39) + ' as ColName, max
(Len(' + sc.name + ')) from tbl'
from syscolumns
where id = object_id('tbl')
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check length for each record for a delimited file 4
importing 4,000 fixed field length records - parsing - removingspaces - etc 3
what should be maximum length of a data base field? 6
Excel Find length of side of triangle in Excel 12
How to adjust physical lengths of fields 5
How can I set each field length? 3
Page Length and Merging Data 3
Exporting Fixed Width Text File 4

Back
Top