InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:38:29 GMT20http://www.sqlservercentral.com/Forums/Topic895432-1631-1.aspxThank 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 columnNumber Of Null RowsProbably 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.[code="sql"]/*Script : Column Size CheckerVersion : 1.0 (March 2010)Author : Richard DoeringWeb : http://sqlsolace.blogspot.com*/SET NOCOUNT ON SET ANSI_WARNINGS ONDECLARE @SCHEMA VARCHAR(50)DECLARE @TABLE VARCHAR(50)SET @SCHEMA = ''SET @TABLE = ''DECLARE @CURRENTROW INTDECLARE @TOTALROWS INTDECLARE @COLUMNMAXSIZE INTDECLARE @COLUMNMINSIZE INTDECLARE @DISTINCT_COL_VAL INTDECLARE @COUNT_COL_NULL INTDECLARE @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_LENGTHFROM SYS.TABLES OINNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = O.OBJECT_IDINNER 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_IDWHERE SCHEMA_NAME(O.SCHEMA_ID) &lt;&gt; 'sys'AND OBJECT_NAME(O.OBJECT_ID) = CASE WHEN @TABLE = '' THEN OBJECT_NAME(O.OBJECT_ID) ELSE @TABLE ENDAND SCHEMA_NAME(O.SCHEMA_ID) = CASE WHEN @SCHEMA = '' THEN SCHEMA_NAME(O.SCHEMA_ID) ELSE @SCHEMA END SELECT @TOTALROWS = COUNT(*) FROM @TABLEDETAILSSELECT @CURRENTROW = 1WHILE @CURRENTROW &lt;= @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 = 0SELECT @SQLSTRINGCOL = 'SELECT @DISTINCT_COL_VAL_0 = COUNT(DISTINCT[' + COLUMN_NAME + ']) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' FROM @TABLEDETAILS WHERE UNIQUEROWID = @CURRENTROWSET @PARAMETERCOL = N'@DISTINCT_COL_VAL_0 INT OUTPUT';EXECUTE SP_EXECUTESQL @SQLSTRINGCOL , @PARAMETERCOL , @DISTINCT_COL_VAL_0 = @DISTINCT_COL_VAL OUTPUTUPDATE @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 NULLSELECT @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 = @CURRENTROWSET @PARAMETERCOLNULL = N'@COUNT_COL_NULL_0 INT OUTPUT';EXECUTE SP_EXECUTESQL @SQLSTRINGCOLNULL , @PARAMETERCOLNULL , @COUNT_COL_NULL_0 = @COUNT_COL_NULL OUTPUTUPDATE @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[/code]Tue, 15 Feb 2011 11:00:49 GMTchris-624650http://www.sqlservercentral.com/Forums/Topic895432-1631-1.aspx[quote][b]tomasiranek (4/13/2010)[/b][hr]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.[/quote]I agree, there are still those of us that are still using SQL2000.Tue, 13 Apr 2010 10:41:14 GMTDougGiffordhttp://www.sqlservercentral.com/Forums/Topic895432-1631-1.aspxIt 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.Tue, 13 Apr 2010 01:36:33 GMTtomasiranekhttp://www.sqlservercentral.com/Forums/Topic895432-1631-1.aspxIt 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.Tue, 13 Apr 2010 00:50:43 GMTerikb68http://www.sqlservercentral.com/Forums/Topic895432-1631-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/69859/">Column Size Checker</A>[/B]Thu, 01 Apr 2010 19:22:38 GMTr5d4