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