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)
- COLUMN_MAX_LENGTH is the defined length of the column.
- DATA_MIN_LENGTH is the lengthof the smallest data found in the column
- 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 
