SQL Server count rows in a database

Problem:

I began analyzing a legacy database and need to get some statistics on what is being used.
I needed to get a feel for which tables were actually being used.

Solution:

select
schema_name(obj.schema_id) + '.' + obj.name AS [TableName],
row_count
from (
    select
        object_id,
        row_count = sum(row_count)
    from sys.dm_db_partition_stats
    where index_id < 2  -- heap or clustered index
    group by object_id
) Q
join sys.tables obj on obj.object_id = Q.object_id
where row_count > 0
order by [TableName]

Source:

http://stackoverflow.com/questions/428458/counting-rows-for-all-tables-at-once