USE pubs

Create Procedure p_CheckSizes (@TableName sysname)
AS
BEGIN
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF

IF Object_ID('v_CheckSizes') IS Not Null
DROP VIEW v_CheckSizes

IF Object_ID('tempdb..##exec') IS Not Null
DROP TABLE ##exec

SELECT ID = Identity(int, 1,1), 
Cmd = Cast ('Create View v_CheckSizes AS ' AS Varchar(255))
INTO ##exec

INSERT ##exec
SELECT 'Select ''L'' Type, Max(Len(['+COLUMN_NAME+'])) MaxLen, ''['+COLUMN_NAME+']'' ColName FROM ['+TABLE_NAME+'] UNION ALL ' Cmd
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND DATA_TYPE in ( 'varchar', 'nvarchar')

INSERT ##exec
SELECT 'Select ''D'' Type, '+Cast(CHARACTER_MAXIMUM_LENGTH as Varchar)+' MaxLen, ''['+COLUMN_NAME+']'' ColName UNION ALL ' Cmd
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND DATA_TYPE in ( 'varchar', 'nvarchar' )

INSERT ##exec (cmd) SELECT 'SELECT ''#'',0, ''#['+@TableName+']#'''

If (Select count(1) From ##exec) > 2 
BEGIN
	EXEC master..xp_execresultset N'SELECT ''''+Cmd+'''' FROM ##exec ORDER BY ID ',N'Pubs'

	DROP TABLE ##exec

	Select 'Unused Space ['+@TableName+'] ' + D.ColName + ' --> ' + Cast (D.MaxLen - L.MaxLen As Varchar(10)) [Unused Space In Column]
	FROM v_CheckSizes L, v_CheckSizes D
	WHERE L.ColName = D.ColName
	AND D.MaxLen <> L.MaxLen
	AND D.Type = 'D'
	AND L.Type = 'L'
	AND L.ColName <> '#['+@TableName+']#'
	ORDER BY ( D.MaxLen - L.MaxLen )
END

END

Run:

master..xp_execresultset 'SELECT ''exec p_CheckSizes ''''''+TABLE_NAME+'''''''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''',N'Pubs'

To generate output similar to:

Unused Space [stores] [stor_name] --> 4
Unused Space [stores] [city] --> 11
Unused Space [stores] [stor_address] --> 21
Unused Space [titles] [title] --> 17
Unused Space [titles] [notes] --> 21

SELECT	[-- Use this query to produce a column list with a count of the number of rows (where used)]
	= CASE WHEN colid = 1 THEN '' ELSE 'UNION ALL ' END 
	+ 'SELECT [Used]=COALESCE((SELECT SUM(CASE WHEN [' 
		+ C.name + '] IS NULL THEN 0 ELSE 1 END) FROM [' + O.name + ']), 0), '
	+ '[' + O.name + ']=''' + C.name + ''''
FROM	dbo.syscolumns C
	JOIN dbo.sysobjects O
		 ON O.id = C.id 
		AND O.type = 'U'
WHERE	    O.name NOT IN ('dtproperties')
--	AND O.name = 'MyTable'
ORDER BY O.name, colid

SELECT	[-- Use this query to produce a column list with Max Length of the data in that column]
	= CASE WHEN colid = 1 THEN '' ELSE 'UNION ALL ' END 
	+ 'SELECT [MaxLen]=COALESCE((SELECT MAX(DATALENGTH([' 
		+ C.name + '])) FROM [' + O.name + ']), 0), '
	+ '[' + O.name + ']=''' + C.name + ''''
FROM	dbo.syscolumns C
	JOIN dbo.sysobjects O
		 ON O.id = C.id
		AND O.type = 'U'
WHERE	    O.name NOT IN ('dtproperties')
--	AND O.name = 'MyTable'
ORDER BY O.name, colid