This script provides a good example of what you are trying to do.
Script is used to analyze table space usage using the output from the sp_spaceused stored procedure
Works with SQL 7.0, 2000, 2005, 2008, 2008 R2. May work with 2012, but I havn't tested it there.
Script to analyze table space usage