April 1, 2010 at 7:22 pm
Comments posted to this topic are about the item Column Size Checker
April 13, 2010 at 12:50 am
It would be usefull if it also included non-text columns. That way one could easilty see the max row size, which can be an issue in for wide denormalized tables used in data marts etc.
April 13, 2010 at 1:36 am
It is good script, but it does not work with old SQL2000 data types like TEXT and NTEXT - and it may be usefull to check these fields as there may be lots of conversions from SQL2000 still comming.
April 13, 2010 at 10:41 am
tomasiranek (4/13/2010)
It is good script, but it does not work with old SQL2000 data types like TEXT and NTEXT - and it may be usefull to check these fields as there may be lots of conversions from SQL2000 still comming.
I agree, there are still those of us that are still using SQL2000.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
February 15, 2011 at 11:00 am
Thank you very much for this script. It almost did everything i needed.
I butchered it a bit as I needed a couple of things added.
Number Of Distinct Values in a column
Number Of Null Rows
Probably not the most elegant way but thought i would post it incase someone else needed it.
It helps when analysing a db your not familiar with as to what columns are used and what are not.
/*
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 @DISTINCT_COL_VAL INT
DECLARE @COUNT_COL_NULL INT
DECLARE @SQLSTRING NVARCHAR(MAX)
DECLARE @SQLSTRINGCOL NVARCHAR(MAX)
DECLARE @SQLSTRINGCOLNULL NVARCHAR(MAX)
DECLARE @PARAMETER NVARCHAR(500);
DECLARE @PARAMETERCOL NVARCHAR(500);
DECLARE @PARAMETERCOLNULL 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,
DISTINCT_COL_VAL INT,
COUNT_NULLS 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'@COLUMNSIZEMIN INT OUTPUT,@COLUMNSIZEMAX 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
/* Add Count Distinct Values In Columns */
SET @DISTINCT_COL_VAL = 0
SELECT @SQLSTRINGCOL = 'SELECT @DISTINCT_COL_VAL_0 = COUNT(DISTINCT[' + COLUMN_NAME + ']) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' FROM @TABLEDETAILS WHERE UNIQUEROWID = @CURRENTROW
SET @PARAMETERCOL = N'@DISTINCT_COL_VAL_0 INT OUTPUT';
EXECUTE SP_EXECUTESQL @SQLSTRINGCOL
, @PARAMETERCOL
, @DISTINCT_COL_VAL_0 = @DISTINCT_COL_VAL OUTPUT
UPDATE @TABLEDETAILS
SET DISTINCT_COL_VAL = ISNULL(@DISTINCT_COL_VAL,0)
WHERE UNIQUEROWID = @CURRENTROW
/* End Add Count Distinct Values In Columns */
/* Add Count Null Values In Columns */
SET @COUNT_COL_NULL = 0
--select count(CASE WHEN REPORTINGGROUP1CODE IS NULL THEN 1 END) from M_TYPEAG WHERE REPORTINGGROUP1CODE IS NULL
SELECT @SQLSTRINGCOLNULL = 'SELECT @COUNT_COL_NULL_0 = COUNT(CASE WHEN [' + COLUMN_NAME + '] IS NULL THEN 1 END) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] IS NULL' FROM @TABLEDETAILS WHERE UNIQUEROWID = @CURRENTROW
SET @PARAMETERCOLNULL = N'@COUNT_COL_NULL_0 INT OUTPUT';
EXECUTE SP_EXECUTESQL @SQLSTRINGCOLNULL
, @PARAMETERCOLNULL
, @COUNT_COL_NULL_0 = @COUNT_COL_NULL OUTPUT
UPDATE @TABLEDETAILS
SET COUNT_NULLS = ISNULL(@COUNT_COL_NULL,0)
WHERE UNIQUEROWID = @CURRENTROW
/* End Count Null Values In Columns */
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
,DISTINCT_COL_VAL
,COUNT_NULLS
FROM @TABLEDETAILS
ORDER BY 1, 2, 3
June 25, 2014 at 8:43 am
Found this very useful today , wasn't able to connect remotely to do a data profile and the network speed would have prevented that anyway so this really helped.
May 17, 2016 at 12:53 pm
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply