The script can be run as-is, and will produce a report on the size of all user tables in the currently selected database. When I find that I need to trim the fat in a database, this report will quickly show where I can gain the most immediately. For example, a large table at the top of the results might have been created for a temporary purpose and can now be dropped, or perhaps is ripe for archiving older rows to a partition devoted to historical data.
The output consists of table name, number of rows, and its physical size, in kilobytes (including unused space), ordered by largest size to smallest.
I reluctantly used cursors for this, working with the assumption that the number of user tables in the database should be relatively small (numbering in the hundreds, or thousands at most), so that cursor performance would not be a problem. As a future improvement, I'd like to possibly convert this to set-based logic.
Update 2/13/09 - Added 'TRUE' param to call to 'sp_spaceused' - this updates the tableusage to provide more accurate results - thanks to Doug (http://www.sqlservercentral.com/Forums/UserInfo437086.aspx). Also changed use of Sysobjects to INFORMATION_SCHEMA.TABLES