Get Row Counts, Column Counts, Data Sizes For All Tables

This script will get the rows counts, column counts (number of columns) and data size for all the tables in your SQL Server database. The results are sorted by the table name in ascending order. If you want to change this sort, you can do so in the ORDER BY clause in the last line of the script.

-- ---------------------------------
-- From: http://mycodetrip.com
-- URL: http://mycodetrip.com/?p=75
-- ---------------------------------
declare @tableStats table (
    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     @tableStats
    exec sp_msforeachtable 'sp_spaceused ''?'''

select     a.table_name,
    a.row_count 'Total Rows',
    COUNT(*) as 'Total Columns',
    CAST(REPLACE(a.data_size, ' KB', '') as integer) 'Data Size (Kb)'
from @tableStats 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
order by   1 asc