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?
-
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.– user1873604Nov 5, 2013 at 2:21
1 Answer
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;
-
Thanks so much! I'll give this a shot as soon as I get to the office this morning. Nov 5, 2013 at 11:15
-
-
@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 extraUNION ALL
at the beginning of the string, which would cause a syntax error.– Hannah Vernon ♦Nov 5, 2013 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). Nov 5, 2013 at 15:03
-