Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Column Size Checker


Column Size Checker

Author
Message
r5d4
r5d4
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 812
Comments posted to this topic are about the item Column Size Checker
erikb68
erikb68
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
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.
tomasiranek
tomasiranek
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 49
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.
DougGifford
DougGifford
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 860
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"
chris-624650
chris-624650
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
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.SUMROWS AS 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


russella0-96423
russella0-96423
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 255
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.



Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8706 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search