/*
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