Optimize Character Field Usage

,

This stored procedure was designed to scan all your character based columns in a particular database and show the minimum, maximum and average data length. As well as give an efficiency rating of the data stored.

Input: None

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.

Side effects: None

·Large database can take upwards of a minute to return data.

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

Rate

Share

Share

Rate