Recommend Decimal Field Sizes

Decimal columns are often declared far wider than the data actually needs. This script scans every decimal column in a schema (or one table), finds the minimum and maximum value actually stored, works out the worst-case width of the whole and fractional parts, and recommends the tightest type that will fit - TinyInt, SmallInt, Int, BigInt or a right-sized Decimal(p, s) - generating the ALTER COLUMN statement for each.

Set @SchemaName and optionally @TableName at the top before running.

SET NOCOUNT ON;

DECLARE @tSQL nVarChar(MAX)
DECLARE @SchemaName nVarchar(128)
DECLARE @TableName nVarchar(128)

SET @SchemaName = 'dbo'
SET @TableName = ''

SELECT
	newid() as Id,
	type_name(SysC.user_type_id) as Type,
	OBJECT_SCHEMA_NAME(SysC.object_id) as [Schema],
	OBJECT_NAME(SysC.object_id) as [Object],
	SysC.name as [Column],
	case
		WHEN type_name(SysC.user_type_id) like 'N%' THEN SysC.max_length / 2
		ELSE SysC.max_length
	END as CurrentMaxLength,
	cast(null as bit) as Nullable,
	cast(null as Decimal(38, 5)) as MinEncountered,
	cast(null as Decimal(38, 5)) as MaxEncountered,
	cast(null as VarChar(50)) as WorstCase
INTO
	#RecordSizes
FROM
	sys.columns as SysC
INNER JOIN sys.types as SysT
	ON SysT.user_type_id = SysC.user_type_id
INNER JOIN sys.objects as SysO
	ON SysO.object_id = SysC.object_id
WHERE
	SysO.type = 'U'
	AND (OBJECT_SCHEMA_NAME(SysO.object_id) = @SchemaName OR NullIf(@TableName, '') IS NULL)
	AND (OBJECT_NAME(SysO.object_id) = @TableName OR NullIf(@TableName, '') IS NULL)
	AND SysT.name in ('decimal')

DECLARE RecordChecks CURSOR FAST_FORWARD FOR
	SELECT
		'UPDATE #RecordSizes SET Nullable = IsNull((SELECT TOP 1 1 FROM [' + [Schema] + '].[' + [Object] + '] WHERE [' + [Column] + '] IS NULL), 0),'
		+ ' MaxEncountered = (SELECT MAX(IsNull([' + [Column] + '], 0)) FROM [' + [Schema] + '].[' + [Object] + ']),'
		+ ' MinEncountered = (SELECT MIN(IsNull([' + [Column] + '], 0)) FROM [' + [Schema] + '].[' + [Object] + '])'
		+ ' WHERE Id = ''' + Convert(VarChar(36), Id)  + ''''
	FROM
		#RecordSizes
OPEN RecordChecks

FETCH FROM RecordChecks INTO @tSQL

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	----------------------------------------------------------------------------------------

	EXEC(@tSQL)

	----------------------------------------------------------------------------------------
	FETCH FROM RecordChecks INTO @tSQL
END--WHILE

CLOSE RecordChecks DEALLOCATE RecordChecks

UPDATE
	#RecordSizes
SET
	WorstCase =
		CASE WHEN
			LEN(Convert(VarChar, MinEncountered)) > LEN(Convert(VarChar, MaxEncountered)) THEN Convert(VarChar, MinEncountered)
			ELSE Convert(VarChar, MaxEncountered)
		END

SELECT
	*,
	CASE WHEN Cast(Fraction as int) = 0
	THEN
		CASE
			WHEN Cast(Whole as Decimal(38)) <= 255 THEN 'TinyInt'
			WHEN Cast(Whole as Decimal(38)) <= 32767 THEN 'SmallInt'
			WHEN Cast(Whole as Decimal(38)) <= 2147483647 THEN 'Int'
			WHEN Cast(Whole as Decimal(38)) <= 9223372036854775808 THEN 'BigInt'
		END
	ELSE
		'Decimal('
		+ Cast( Len(Whole) + Len(Replace(Fraction, '0', ' ')) as varchar)
		+', '
		+ Cast(Len(Replace(Fraction, '0', ' ')) as VarChar) + ')'
	END as DataType,
	'ALTER TABLE  [' + [Schema] + '].[' + [Object] + ']'
	+ ' ALTER COLUMN [' + [Column] + '] '

	+ CASE WHEN Cast(Fraction as int) = 0
	THEN
		CASE
			WHEN Cast(Whole as Decimal(38)) <= 255 THEN 'TinyInt'
			WHEN Cast(Whole as Decimal(38)) <= 32767 THEN 'SmallInt'
			WHEN Cast(Whole as Decimal(38)) <= 2147483647 THEN 'Int'
			WHEN Cast(Whole as Decimal(38)) <= 9223372036854775808 THEN 'BigInt'
		END
	ELSE
		'Decimal('
		+ Cast( Len(Whole) + Len(Replace(Fraction, '0', ' ')) as varchar)
		+', '
		+ Cast(Len(Replace(Fraction, '0', ' ')) as VarChar) + ')'
	END
	+ ' ' + CASE WHEN Nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END as AlterScript
FROM
	(
		SELECT
			Type,
			[Schema],
			[Object],
			[Column],
			Nullable,
			WorstCase,
			Left(WorstCase, CharIndex('.', WorstCase) - 1) as Whole,
			Right(WorstCase, LEN(WorstCase) - CharIndex('.', WorstCase)) as Fraction
		FROM
			#RecordSizes
	) as Info

DROP TABLE #RecordSizes