Legth of all columns in db

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

I am trying to get a list of each column name in a particular table,
and the maximum data element within each of the columns. So if one
column had two entries, and one was three characters long and the
other was thirty, I would expect it to return thirty.

Thanks.

JR
 
Create a query like:
SELECT Max(Len([Field1])) as MaxLenField1, Max(Len([Field2])) as MaxLenField2
FROM [a particular];
 
SELECT Max(Len(Field1)) as LongestField1
, Max(Len(Field2)) as LongestField2
, Max(Len(field3)) as LongestField3
FROM YourTable

Warning: This will be probably be very slow - depending on the number of
fields and the number of records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT Max(Len(Field1)) as LongestField1
, Max(Len(Field2)) as LongestField2
, Max(Len(field3)) as LongestField3
FROM YourTable

Warning: This will be probably be very slow - depending on the number of
fields and the number of records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.






- Show quoted text -
Click to expand...


Thanks for the replies. So I'm guessing from the answers that there
is no way to do some sort of multi-select to pull the columns from a
table and determine the max length of each automatically? I have over
80 columns so other then some potential creative cut and paste, this
could be annoying. Is there a table that lists all the columns for a
particular table in Access?
 
IMHO 80 columns is about 50 too many. However, you can write a function to
write the SQL. Paste this code into a new modules and save the module as
"basQueryCode". View the immediate windowwwww (press Ctrl+G) and type:
?BuildMaxLen("Your80FieldTableName")

Function BuildMaxLen(pstrTable As String) As String
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fl As DAO.Field
Dim strSQL As String
Set db = CurrentDb
Set td = db.TableDefs(pstrTable)
strSQL = "SELECT "
For Each fl In td.Fields
strSQL = strSQL & "Max(Len([" & fl.Name & "])) as [MaxLen" & fl.Name
& "], " & vbCrLf
Next
strSQL = Left(strSQL, Len(strSQL) - 4) & " " & vbCrLf & "FROM [" &
pstrTable & "];"
Set fl = Nothing
Set td = Nothing
Set db = Nothing
BuildMaxLen = strSQL
End Function
 
Back
Top