Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column Size Checker Expand / Collapse
Author
Message
Posted Thursday, April 01, 2010 7:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 8:26 AM
Points: 267, Visits: 674
Comments posted to this topic are about the item Column Size Checker
Post #895432
Posted Tuesday, April 13, 2010 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 12:49 AM
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.
Post #902155
Posted Tuesday, April 13, 2010 1:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 12:07 AM
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.
Post #902179
Posted Tuesday, April 13, 2010 10:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:04 PM
Points: 127, Visits: 858
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"
Post #902610
Posted Tuesday, February 15, 2011 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 17, 2012 3:03 PM
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

Post #1064443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse