3

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?

1
  • I am looking for one single number to be returned, as well as the table and column name that contains the string that is found. – user1873604 Nov 5 '13 at 2:21
9

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;
9
  • Thanks so much! I'll give this a shot as soon as I get to the office this morning. – user1873604 Nov 5 '13 at 11:15
  • What is @sep for? – Aaron Bertrand Nov 5 '13 at 14:56
  • @sep is used to add the ` UNION ALL ` between each successive SELECT .... I could hard code it in as @cmd = @cmd + ' UNION ALL ' + ... but then I'd end up with an extra UNION ALL at the beginning of the string, which would cause a syntax error. – Hannah Vernon Nov 5 '13 at 14:58
  • Oh I was confused because you don't assign a value to @sep until inside the query - I didn't look there. I usually use STUFF to eliminate the first UNION ALL (or comma, or whatever the concatenation is). – Aaron Bertrand Nov 5 '13 at 15:03
  • @user1873604 - did this work for you? – Hannah Vernon Nov 5 '13 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.