Last week I struggled a lot looking for a code or something similar regarding the space stored and the space real used for each table and column in my db. I didn't find anything really interesting and so I came up with a nice solution. First at all I created a table Checkcolumn that retrieves the data from the system tables Object, Column and Type. For this article I chose only the type text (char, nchar, nvarchar, etc). The issue was to join the real situation with the Checkcolumn. So I had to create another table that retrieves the real data (max length, avg length, scount column, etc.) The second table is called Checkcolumnfinal. Once I had the two tables I needed to join them in another table, Checkthecolumn, the final table that compares the System tables with the Running tables. I included all in a stored procedure and I tried with all my databases. It works pretty good and it gives me a deep view of my design ( After running this procedure I discovered how much space I was wasting). The last step was to create a report with all data from Checkthecolumn. I enjoyed a lot building graphs and statistics but that's another article that I would put in SSRS Articles. 

The code:
Create procedure mySP_CheckColumnSize as
IF object_id('Checkcolumn','U') is not null
    begin
        Drop table CheckColumn
    end
    IF object_id('Checkcolumnfinal','U') is not null
    begin
        Drop table CheckColumnfinal
    end
    IF object_id('Checkthecolumn') is not null
    begin
        Drop table ChecktheColumn
    end
create table Checkcolumn (iddnum int identity, tabledb nvarchar(100), columndb nvarchar (100), maxlength int, typename char(20),  upd int default (1) )
INSERT INTO CHECKCOLUMN (tabledb,columndb,maxlength, typename) select o.name AS TT,s.name AS CC, s.max_length, t.name as typename
from sys.columns s inner join sys.objects o on s.object_id=o.object_id inner join sys.types t on s.system_type_id=t.system_type_id
where o.type='U' and t.system_type_id in (35,99,167,175,231,239) and t.name <> 'sysname'  and o.name<>'Checkcolumn'
create table Checkcolumnfinal (numcol int, tablecheck nvarchar(100), columncheck nvarchar (100), maxlengthcheck int, sumlengthcheck int, avglengthcheck int )
declare @co int
select @co=count(*) FROM checkcolumn
declare @loop int
set @loop=1
while (@loop<=@co)
begin
declare @table2 varchar(100)
declare @column2 char (100)
declare @max char (30)
declare @avg char (30)
declare @tostart nvarchar(512)
select top 1 @table2=tabledb, @column2=columndb from checkcolumn where upd=1
set @tostart = 'select count(*), ''' + @table2 + ''', ''' + @column2 + ''', max(len('+@column2+')), sum(len('+@column2+')), avg(len('+@column2+'))  from '+@table2
INSERT INTO checkcolumnfinal (numcol, tablecheck, columncheck, maxlengthcheck, sumlengthcheck, avglengthcheck)
exec(@tostart)
update checkcolumn set upd=0 where tabledb=@table2 and columndb=@column2
set @loop=@loop+1
end
create table Checkthecolumn (tabledb nvarchar(100), columndb nvarchar(100), typename nvarchar(30), maxlength int , maxcheck int,
avgcheck int, maximum int, usage int, notused int)
INSERT INTO Checkthecolumn (tabledb,columndb,typename,maxlength,maxcheck,avgcheck,maximum,usage,notused)
select tabledb, columndb, typename, maxlength, maxlengthcheck,
avglengthcheck,   (numcol*maxlength) as maximum , sumlengthcheck as usage, ((numcol*maxlength)-sumlengthcheck) as notused
from checkcolumn c1 inner join checkcolumnfinal c2 on c1.tabledb=c2.tablecheck and c1.columndb=c2.columncheck

this code create the three tables but more important create the Checkthecolumn that returns you data like (using Adventureworks):



Below a simple sample how you can show the data in a report ( you can create as many report as you want, with charts, lines, calculation, etc.)



Try the code and start to clean your databases, it's easy. It's useful.