Returns a table list which have maximum length of column.
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
)
);
Other reference:
