Tables with at least one LOB (max) column

 

Retrieving Tables with Maximum Column Lengths

In SQL Server, if you're looking to get a list of tables that have columns with the maximum length, you can use the following query:

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
    SELECT 1 
    FROM [sys].columns c
    WHERE c.[object_id] = t.[object_id]
    AND c.max_length = -1
    AND c.system_type_id IN
    (
        165, -- varbinary
        167, -- varchar
        231  -- nvarchar
    )
);

This SQL query returns a list of tables where the columns are defined with a maximum length. It checks the system columns for types like varbinary, varchar, and nvarchar, ensuring that the column length is set to its maximum possible value.

Additional Resources

For further details on SQL Server table and row counts, you can refer to this helpful post on Himanshu Patel's blog.