Technical Article

Column Size Checker

,

Introduction :

This is a utility to show how well your column sizes suit your data (or vice versa)..

Usage :

By default, the script will run for every table in the database you run it in.

This may prove quite time consuming so set the @SCHEMA and @TABLE variables at the top of the script to the schema and table name respectively to analyse only one table.

Output :

The output is fairly self explanatory i.e. the name of the name, it's rowcount and column details.

For each column, 3 length values are given (where appropriate)

  1. COLUMN_MAX_LENGTH is the defined length of the column.
  2. DATA_MIN_LENGTH is the lengthof the smallest data found in the column
  3. DATA_MAX_LENGTH is the length of the largest data found in the column

I had fun creating it, I hope someone (who isn't using SSIS 2008 Data Profiler!) finds it useful.

 

Richard Doering

http://sqlsolace.blogspot.com

/*
Script    : Column Size Checker
Version   : 1.0 (March 2010)
Author    : Richard Doering
Web       : http://sqlsolace.blogspot.com
*/
SET NOCOUNT ON 
SET ANSI_WARNINGS ON
DECLARE @SCHEMA  VARCHAR(50)
DECLARE @TABLE  VARCHAR(50)

SET @SCHEMA = ''
SET @TABLE  = ''

DECLARE  @CURRENTROW INT
DECLARE  @TOTALROWS INT
DECLARE  @COLUMNMAXSIZE INT
DECLARE  @COLUMNMINSIZE INT
DECLARE  @SQLSTRING NVARCHAR(MAX)
DECLARE  @PARAMETER NVARCHAR(500);
DECLARE  @TABLEDETAILS 
  TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
     TABLE_SCHEMA    VARCHAR(255),
     TABLE_NAME      VARCHAR(255),
     COLUMN_NAME     VARCHAR(255),
     COLUMN_TYPE     VARCHAR(255),
     TABLE_ROWS     BIGINT,
     MAX_LENGTH      INT,
     DATA_MIN_LENGTH INT,
     DATA_MAX_LENGTH INT)
       
INSERT INTO @TABLEDETAILS
          (TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME,
           COLUMN_TYPE,
           TABLE_ROWS,
           MAX_LENGTH)

SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
      OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
      C.NAME                    AS COLUMN_NAME,
      T.NAME                    AS COLUMN_TYPE,
      R.SUMROWSAS TABLE_ROWS,
      C.MAX_LENGTH
FROM   SYS.TABLES O
INNER JOIN SYS.COLUMNS C
       ON C.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.TYPES T
       ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
      AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
INNER JOIN (SELECT OBJECT_ID, SUM(ROWS) AS SUMROWS FROM SYS.PARTITIONS WHERE INDEX_ID IN (0,1) GROUP BY OBJECT_ID) R
ON R.OBJECT_ID = O.OBJECT_ID
      
WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
AND OBJECT_NAME(O.OBJECT_ID) = CASE WHEN @TABLE = '' THEN OBJECT_NAME(O.OBJECT_ID) ELSE @TABLE END
AND SCHEMA_NAME(O.SCHEMA_ID) = CASE WHEN @SCHEMA = '' THEN SCHEMA_NAME(O.SCHEMA_ID) ELSE @SCHEMA END
                                
SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
SELECT @CURRENTROW = 1
WHILE @CURRENTROW <= @TOTALROWS   
BEGIN     
SET @COLUMNMAXSIZE = 0     
SET @COLUMNMINSIZE = 0  
SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMIN = MIN(LEN([' + COLUMN_NAME + '])) ,@COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WITH (NOLOCK)'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT,@COLUMNSIZEMIN INT OUTPUT';          
EXECUTE SP_EXECUTESQL       @SQLSTRING 
                          , @PARAMETER 
                          , @COLUMNSIZEMIN = @COLUMNMINSIZE OUTPUT   
                          , @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
UPDATE @TABLEDETAILS     
SET    DATA_MAX_LENGTH = ISNULL(@COLUMNMAXSIZE,0)  , DATA_MIN_LENGTH = ISNULL(@COLUMNMINSIZE,0)   
WHERE  UNIQUEROWID = @CURRENTROW                               

SET @CURRENTROW = @CURRENTROW + 1   
END      

SELECT   TABLE_SCHEMA
        ,TABLE_NAME
        ,TABLE_ROWS
        ,COLUMN_NAME
        ,COLUMN_TYPE
        ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
        ,DATA_MIN_LENGTH 
        ,DATA_MAX_LENGTH 
FROM @TABLEDETAILS 
ORDER BY 1, 2, 3

Rate

4.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4.75 (4)

You rated this post out of 5. Change rating