Efficient way to find the max datalength of LOB columns (NVARCHAR(MAX), XML) across a database?

Hi everyone,

I am trying to audit my database to find columns defined as NVARCHAR(MAX), VARCHAR(MAX), or XML that are actually storing very large amounts of data.

I have a script that iterates through tables and runs a query like this for every MAX column:

SELECT MAX(DATALENGTH(MyColumn)) FROM MyTable

I realized that DATALENGTH combined with MAX forces a full table scan. On my larger tables (millions of rows), this is causing massive I/O spikes. Is there a way to get this specific metric (max size of a single cell) without reading every page of the table?

Thanks for any advice!