Recommend String Field Sizes

The string sibling of the decimal size recommender: this script scans every varchar/nvarchar column in a schema (or one table), measures the longest value actually stored, rounds it up to a sensible size (2, 4, 8, 10, 20, 30, 40, 50, 60, 64, 100, 128, 200, 256, 512, 1024, then multiples of 1024 beyond that), and generates an ALTER COLUMN statement for every column whose declared size does not match the recommendation. Anything at or above @MaxSize is recommended as MAX.

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

SET NOCOUNT ON;

DECLARE @tSQL nVarChar(MAX)
DECLARE @MaxSize int --The size at which we default to "MAX".
DECLARE @SchemaName nVarchar(128)
DECLARE @TableName nVarchar(128)

SET @SchemaName = 'dbo'
SET @TableName = ''
SET @MaxSize = 1000

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 SysC.max_length = -1 THEN @MaxSize
		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 int) as MaxEncountered
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 ('nvarchar', 'varchar')

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(LEN([' + [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

SELECT
	*,
	'ALTER TABLE  [' + [Schema] + '].[' + [Object] + ']'
	+ ' ALTER COLUMN [' + [Column] + '] [' + [Type] + '](' + CASE WHEN Recommended >= @MaxSize THEN 'MAX' ELSE Cast(Recommended as VarChar) END + ')'
	+ ' ' + CASE WHEN Nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END as AlterScript
FROM
	(
		SELECT
			Type,
			[Schema],
			[Object],
			[Column],
			CurrentMaxLength,
			Nullable,
			MaxEncountered,
			CASE
				WHEN MaxEncountered <= 2 THEN 2
				WHEN MaxEncountered <= 4 THEN 4
				WHEN MaxEncountered <= 8 THEN 8
				WHEN MaxEncountered <= 10 THEN 10
				WHEN MaxEncountered <= 20 THEN 20
				WHEN MaxEncountered <= 30 THEN 30
				WHEN MaxEncountered <= 40 THEN 40
				WHEN MaxEncountered <= 50 THEN 50
				WHEN MaxEncountered <= 60 THEN 60
				WHEN MaxEncountered <= 64 THEN 64
				WHEN MaxEncountered <= 100 THEN 100
				WHEN MaxEncountered <= 128 THEN 128
				WHEN MaxEncountered <= 200 THEN 200
				WHEN MaxEncountered <= 256 THEN 256
				WHEN MaxEncountered <= 512 THEN 512
				WHEN MaxEncountered <= 1024 THEN 1024
				ELSE
					CASE
						WHEN (MaxEncountered % 1024) != 0 THEN MaxEncountered + (1024 - (MaxEncountered % 1024))
						ELSE MaxEncountered
					END
			END as Recommended
		FROM
			#RecordSizes
	) as Info
WHERE
	CurrentMaxLength != Recommended

DROP TABLE #RecordSizes