A (Huge) Needle In A Bigger Haystack

Microsoft's Sync Framework is a fragile creature, and as such doesn't like being abused with data. Today I ran into a problem with our app trying to sync a record that had a field of 8266 characters, which is over the (apparent) 8192 character limit. To simplify the problem of finding which record out of millions scattered across hundreds of tables with dozens of columns, I first hit google like any lazy coder.

This thread got me started by giving me a base for searching a single table for the longest column:
http://www.dbforums.com/microsoft-sql-server/1069468-possible-get-max-length-text-field.html

...but I needed more.

So I wrapped that thing in another cursor, and came up with this:

DECLARE @table SYSNAME, @field SYSNAME


DECLARE b_cursor CURSOR STATIC
FOR
SELECT name FROM sys.tables WHERE name not like '%tracking'  -- customize table SELECTion here
OPEN b_cursor
FETCH NEXT FROM b_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN


CREATE TABLE #Temp (TableName SYSNAME, ColumnName SYSNAME, MaxLength INT)


DECLARE a_cursor CURSOR STATIC
FOR
SELECT name FROM syscolumns WHERE id = object_id(@table) and name like '%history%' -- customize column SELECTion here
OPEN a_cursor


FETCH NEXT FROM a_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp
EXEC ('SELECT ''' + @table + ''', ''' + @field + ''', max(len(' + @field + ')) FROM ' + @table )
FETCH NEXT FROM a_cursor INTO @field
END


CLOSE a_cursor
DEALLOCATE a_cursor


SELECT * FROM #Temp


DROP TABLE #Temp


FETCH NEXT FROM b_cursor INTO @table
END
CLOSE b_cursor
DEALLOCATE b_cursor


--DROP TABLE #Temp


-- select from a table to see the record with the largest field
SELECT * FROM (SELECT Id, len(History) AS len FROM WorkOrders) AS a ORDER BY len DESC

The guts of it is the EXEC statement that queries each column of each table. Note that I've put in comments where you can customize the queries to filter tables and columns by name, etc. At the bottom is a query that shows how to query a table for records and sort by the record size. Emjoy

** Note: If it complains about the table #Temp already existing, just drop it by running the commented drop statement towards the end.