Script to compare actual data length to column width

A quick review of a customer database revealed many inappropriate maximum lengths on character columns. When dealing with fixed width columns, grossly oversized columns negatively impact performance because fewer rows fit into a page, which means more pages need to be read. Here's a query you can run to compare actual data lengths to maximum defined column widths.

DECLARE @TABLE_SCHEMA NVARCHAR(128);

DECLARE @TABLE_NAME NVARCHAR(128);

DECLARE @COLUMN_NAME NVARCHAR(128);

DECLARE @PARMS NVARCHAR(100);

DECLARE @DATA_TYPE NVARCHAR(128);

DECLARE @CHARACTER_MAXIMUM_LENGTH INT;

DECLARE @MAX_LEN NVARCHAR(10);

DECLARE @TSQL NVARCHAR(4000);

DECLARE DDLCursor CURSOR FAST_FORWARD FOR

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN
    (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

AND DATA_TYPE IN ('char','nchar','varchar','nvarchar')

AND CHARACTER_MAXIMUM_LENGTH > 1

OPEN DDLCursor;

SET @PARMS = N'@MAX_LENout nvarchar(10) OUTPUT';

CREATE TABLE #space (

     TABLE_SCHEMA NVARCHAR(128) NOT NULL

   , TABLE_NAME NVARCHAR(128) NOT NULL

   , COLUMN_NAME NVARCHAR(128) NOT NULL

   , DATA_TYPE NVARCHAR(128) NOT NULL

   , CHARACTER_MAXIMUM_LENGTH INT NOT NULL

   , ACTUAL_MAXIMUM_LENGTH INT NOT NULL

);

-- Perform the first fetch.

FETCH NEXT FROM DDLCursor

INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

   SET @tsql = 'select @MAX_LENout = cast(max(len(isnull('

             + QUOTENAME(@COLUMN_NAME) + ',''''))) as nvarchar(10)) from '

             + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME);

   EXEC sp_executesql @tsql, @PARMS, @MAX_LENout = @MAX_LEN OUTPUT;

   IF CAST(@MAX_LEN as int) < @CHARACTER_MAXIMUM_LENGTH  -- not interested if lengths match

   BEGIN

       SET @tsql = 'insert into #space values ('''

                 + @TABLE_SCHEMA + ''','''

                 + @TABLE_NAME + ''','''

                 + @COLUMN_NAME + ''','''

                 + @DATA_TYPE + ''','

                 + CAST(@CHARACTER_MAXIMUM_LENGTH as nvarchar(10)) + ','

                 + @MAX_LEN + ')';

       EXEC sp_executesql @tsql;

   END;

   -- This is executed as long as the previous fetch succeeds.

   FETCH NEXT FROM DDLCursor INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH;

END;

CLOSE DDLCursor;

DEALLOCATE DDLCursor;

select * from #space