Determining Table Sizes and the Number of Rows in each Table in a SQL DB

The following script can be used to determine which tables in a database are taking up the most physical space and which have the most rows in them.  The query below orders the results by tables size from largest to smallest, but it's easily updatable to order by row count.  If you're interested in seeing which tables in your database are growing the most, you can create a nightly job to dump the results of this query into a table with a timestamp added.

/*

************************************************
Author:  Chicago Computer Consultants
Date:  5.6.11
Title:  Number of Rows and Table Sizes
Purpose:  Displays the names of all of the tables, numbers of rows, and indexes in a SQL db and their sizes.
************************************************
*/

USE [/*Insert DB Name Here*/]

GO

CREATE TABLE

  #temp (
   table_name sysname ,
   row_count int,
   reserved_size varchar(50),
   data_size varchar(50),
   index_size varchar(50),
   unused_size varchar(50))

SET NOCOUNT ON

INSERT    
    #temp
EXEC      
    sp_msforeachtable 'sp_spaceused ''?'''
SELECT    
    a.table_name AS [Table Name],
    a.row_count AS [Row Count],
    count(*) AS [Column Count],
    a.data_size AS [Data Size],
    a.index_size AS [Index Size]
FROM      
 #temp a
 INNER JOIN information_schema.columns b
  ON
   a.table_name collate database_default = b.table_name collate database_default
GROUP BY  
  a.table_name,
  a.row_count,
  a.data_size,
  a.index_size
ORDER BY  
  CAST(Replace(a.data_size, ' KB', '') as integer) desc
DROP TABLE #temp