CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
Prady
Prady

Reputation: 11330

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, how?

Upvotes: 7

Views: 36623

Answers (3)

ShelS
ShelS

Reputation: 187

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'

Upvotes: 0

pmac72
pmac72

Reputation: 9202

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

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

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)

Upvotes: 19

Related Questions