how to get the max size used by a field in table

问题:

I have a field which has been set to max size. How can i find the max size occupied by the field.

For example if the records are for table TableA

FieldA

123
abcd
1234567

I need to know which row occupied the most size and what the size is

Thanks

Prady

回答1:

LEN tests for the length in characters, e.g. "a" = 1 char

select max(len(fieldA)) from tbl

DATALENGTH checks for the size in bytes, an NVarchar occupies 2 bytes per character

select max(datalength(fieldA)) from tbl

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

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

回答2:

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

回答3:

You may to 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'