How to get length of data in Text,NText and Image columns?

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types .

So to solve this problem
SQL Server has a DATALENGTH() function.

For e.g.

SELECT name, LEN(packagedata) FROM dbo.sysdtspackages

this query will give error.

the correct query is

SELECT name, DATALENGTH(packagedata) FROM dbo.sysdtspackages

Enjoy!