Sql-server – How to find the longest string in a row amongst various text fields

sql serversql-server-2008-r2

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?

Best 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;

Related Solutions

Why Row Overflow Not Working on SQL Server 2005

This is just a warning message. You can verify that rows are overflowing to the ROW_OVERFLOW_DATA pages by using the undocumented command DBCC IND.

Personally this sounds like a table which could stand some normalization.

SQL Server – Why ALTER COLUMN to NOT NULL Causes Log File Growth

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done in a single pass, though, the log has to account for all of the changes so that, if you hit cancel, it knows exactly what to undo.

An example. Simple table:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Now, let's look at the page details. First we need to find out what page and DB_ID we're dealing with. In my case I created a database called foo, and the DB_ID happened to be 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

The output indicated that I was interested in page 159 (the only row in DBCC IND output with PageType = 1).

Now, let's look some select page details as we step through the OP's scenario.

DBCC PAGE(5, 1, 159, 3);

enter image description here

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;    
DBCC PAGE(5, 1, 159, 3);

enter image description here

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

enter image description here

Now, I don't have all the answers to this, as I am not a deep internals guy. But it's clear that - while both the update operation and the addition of the NOT NULL constraint undeniably write to the page - the latter does so in an entirely different way. It seems to actually change the structure of the record, rather than just fiddle with bits, by swapping out the nullable column for a non-nullable column. Why it has to do that, I'm not quite sure - a good question for the storage engine team, I guess. I do believe that SQL Server 2012 handles some of these scenarios a lot better, FWIW - but I have yet to do any exhaustive testing.

Best Answer

Related Solutions

Why Row Overflow Not Working on SQL Server 2005

SQL Server – Why ALTER COLUMN to NOT NULL Causes Log File Growth

Related Question