Optimize Character Field Usage

Output: Table name,
   Column name,
   Defined datatype and length,
   Average length of data for that column,
      Minimum length of data for that column,
   Maximum length of data for that column,
   Efficiency Rating for that column.

create proc sp_GetCharDataLengthAnalysis
AS

/* Written by Robert Vallee 08/7/2001 www.hybridx.com
**This proc will work on system databases but will sometimes give errors.
It was designed for user databases to scan character definded columns.

   Output: Table name, 
   Column name, 
   Defined datatype and length, 
   Average length of data for that column, 
      Minimum length of data for that column, 
   Maximum length of data for that column, 
   Efficiency Rating for that column.

   Efficiency Rating:
   Maximum length of data for that column(Max Length)/defined length = Efficiency Rating 
   This calculation is based on the assumption that if you have a column defined as
   varchar(100) and at least one of the values you are storing is 100 characters long then you must need 
   the whole 100 characters you defined, even if the rest of the data being stored is less than the maximum.

*/
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @str varchar(8000)

CREATE TABLE #d1([Table] varchar(50),[Column] varchar(65),[DataType] char(17),
[Avg Length] int,[Min Length] int,[Max Length] int, [Column Usage Rating] decimal(10,3))

DECLARE @tbl varchar(50)
DECLARE @col varchar(65)
DECLARE @type char(17)
DECLARE CharLenX CURSOR SCROLL KEYSET FOR 

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColName, dbo.systypes.name + 
'(' + CONVERT(varchar(4), dbo.syscolumns.length)+ ')' AS Datatype
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE (dbo.sysobjects.xtype = 'u') AND (dbo.systypes.name IN (N'char', N'varchar', N'nchar', N'nvarchar'))

OPEN CharLenX

FETCH FIRST FROM CharLenX INTO @tbl,@col,@type

WHILE @@FETCH_STATUS = 0
BEGIN 

SET @str = 'select ' + '''' + @tbl + '''' + ' as [Table], ' + '''' + @col + '''' + ' as [Column],' +
 '''' + @type + '''' + ' as [DataType], ((select sum(len( ' + @col + ' )) from ' + @tbl + ' ) / (select count(*) from ' +
 @tbl + ' )) as [Avg Length], 
(select min(len( ' + @col + ' )) from ' + @tbl + ' ) as [Min Length],
(select max(len( ' + @col + ' )) from ' + @tbl + ' ) as [Max Length],
(select CONVERT(decimal(10,3), max(len( ' + @col + ' ))) / CONVERT(decimal(10,3),(col_length( ' + '''' + @tbl + '''' + ',' + '''' + @col + '''' + '))) from ' + @tbl + ' ) as [Column Usage Rating]'

FETCH NEXT FROM CharLenX INTO @tbl,@col,@type
IF @@FETCH_STATUS = 0
BEGIN

INSERT INTO #d1
EXEC (@str)

END
END

CLOSE CharLenX
DEALLOCATE CharLenX 

SET NOCOUNT ON
SELECT [Table],[Column],[DataType],[Avg Length],[Min Length],[Max Length],[Column Usage Rating]as [Efficiency Rating(1.000 is Optimal)]  FROM #d1
ORDER BY [table],[column]

DROP TABLE #d1




GO