How to get the maximum size used by a field in a table

Question

I have a field for which the maximum size is set. How can I find the maximum size occupied by the field.

For example, if the records for table TableA

FieldA 123 abcd 1234567 

I need to know which row occupied the largest size and what size

thanks

Prady

Answer 1

LEN checks the length in characters, for example. "a" = 1 char

 select max(len(fieldA)) from tbl 

DATALENGTH checks for size in bytes, NVarchar takes 2 bytes per character

 select max(datalength(fieldA)) from tbl 

To get all the rows in the table that have the maximum data length in FieldA,

 select * from tbl join (select MAX(LEN(fieldA)) maxlen from tbl) l on l.maxlen = LEN(tbl.fieldA) 

Answer 2

 SELECT TOP 1 WITH TIES * FROM tbl ORDER BY len(tbl.fieldA) DESC 

Answer 3

You can query this sql

 Select Character_Maximum_Length From INFORMATION_SCHEMA.COLUMNS Where TABLE_CATALOG Like 'DatabaseName' And TABLE_NAME Like 'TableName' And COLUMN_NAME Like 'FieldName'