How do I find the longest string in a row amongst various text fields?

Question

Does anyone have an easy query to find the longest string of characters in a data row from ALL alphanumeric (text, nchar, varchar, etc) type columns from ALL tables in a single SQL Server 2008 R2 database?

Answer 1

The following code should return a single column that has the largest piece of text in the current database, along with the Schema, Table, and Column name, and the size of the piece of text:

DECLARE @cmd NVARCHAR(max);
DECLARE @sep NVARCHAR(max);
SET @cmd = '';
SET @sep = '';
SELECT @cmd = @cmd + @sep +
    'SELECT ''' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name) + 
    '.' + QUOTENAME(c.name) + ''' AS ColumnName, ' + 
    QUOTENAME(c.name) + ' AS ColumnValue, LEN(CAST(' + 
    QUOTENAME(c.name) + ' AS NVARCHAR(max))) AS ColumnLength ' +
    ' FROM ' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name)
    , @sep = ' UNION ALL '   
FROM sys.tables t 
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty on c.system_type_id = ty.system_type_id
    INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE t.is_ms_shipped = 0
    AND ty.name IN (
        'ntext'
        , 'text'
        , 'varchar'
        , 'nvarchar'
        , 'nchar'
        , 'char'
        , 'sysname'
        , 'sql_variant'
        );
SET @cmd = 'SELECT TOP(1) * FROM (' + @cmd + ') t ORDER BY 3 DESC'
SELECT @cmd; /* This displays the resulting SQL Text that will be 
            EXEC'd by the following statement */
EXEC sp_executesql @cmd;