THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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

 

 

 

Published Thursday, November 12, 2009 2:33 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

tim said:

Nice but the line

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

eliminates Varchar(Max)  so I reversed to

IF CAST(@MAX_LEN as int) > @CHARACTER_MAXIMUM_LENGTH  

because that is the Colum type I needed info on  

August 30, 2012 2:25 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement