To measure the length of VARCHAR fields the function LEN(varcharfield) is useful. For the length of text field data, you need DATALENGTH instead.
To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field.
Example:
SELECT DATALENGTH(yourtextfield) AS TEXTFieldSize
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
SQL Server slow? Let's fix it together in about four hours.
In my Comprehensive Database Performance Health Check, you share your screen, I guide you, and we fix your biggest performance troublemakers. No passwords. No remote access.
You keep every script, and the know-how to fix it yourself next time.
Want your whole team to learn it?
My Practical Workshop has no PowerPoint, just live demos. My AI Enablement Workshop shows your team where AI saves hours, and where it quietly costs them.
Over 400 customers have worked with me this way, and not one shared a password. See all consulting options.
84 Comments. Leave new
Really useful
Just what I wondered about.
Hi, It is really useful.
I have the database – in which i have to generate script for 10 tables only – as i dont have Enterprise Manager, i want to generate the script through Query Analyser onl.
ie the script should like the result of –Enterprise Manager—> Select the Table(s)–Rt Click–All Tasks — Generate SQL Script.
How can i get this?
Thanks in Advance.
Regards — Poov
Thnx!!
This was just what I needed
xx
Thank you! Before this I found a bunch of other SQL’s _languages_ that accept just LENGTH, but THIS is what I really needed.
Thank you!
This is what I was looking for.
Thanks
Nice …
but now how would i do that in Hibernate …
: )
Thanks for the info! Just what I was looking for…
Thanks :) You saved me lots of trouble…
I need some statement to check database field size in the code before inserting any value
@Antonio,
You can use this stord procedure
SP_HELP table_name
it will list out all the fields, their sizes, constraints, indexes….
It gives very good information.
Hope this helps.
Thanks,
Imran.
This is the most helpful thing EVER. Thank you!
thanks , very useful
Thanks! You are the SQL man!
Vey helpful dude!! Keep up the good work!
Jai Hind!
Thank you sir! Just what I was looking for!
Thanks. Short and usefull info.
Really useful…
i want to store more than 8000 chars in table field..
i hv used varchar(max) , text(max) ,but i m not able to get success in it..
everytime data gets truncated whn i insert it..
so can u tell me how to overcome from this problem..?
Note that there is no such datatype as text(max)
How did you insert data?
Can you post the code?
Are you use access database?
if yes then take data type memo.
if you use sqlserver 2005/08 there are no text data type
there you can use nvarchar(max)
I am having the same issue as Ramani. Any solution for this ?
I have a text field and what ever number of characters that I save on that field, it returns only 1001 characters. But when I checked with DATALENGTH() function it shows that it is having the same number of characters that I entered.
@Chaminda,
Open SQL Server Management Studio
Go to Tools – Options (Tools exists at the top left of SSMS)
Expand Query Results
Expand SQL Server
Click on Results to Grid
On right side, Set Value for :
Maximum Characters retrieved : Set a higher value.
Because of this, SQL Server is returning fewer characters. Change this value to a higher value and see the difference
~ IM.