Here are a couple of quick database and table level queries that I have found useful lately.

How many rows in each table of a database?

The first temptation with this one was to use something like sp_MSforeachtable with a count(*), until I recalled that the catalog view sys.indexes has a row count as one of the columns. The view is database scoped so will return all indexes in the current database context. Books on line has the following index types as at SQL Server 2008R2:

0 = Heap

1 = Clustered

2 = Nonclustered

3 = XML

4 = Spatial

So if we filter on index types of 0 or 1 we will catch every table with no duplication.

Joining sys.indexes with sys.objects (which is also database scoped) allows us to filter out any system objects and just look at user tables, and also pick up some friendly names:

use <database name>
go
SELECT
DB_NAME() as 'Database',
SCHEMA_NAME(o.uid) as 'Schema',
    o.Name as 'Table'
    , i.Rows
FROM
    sysobjects o
    INNER JOIN sysindexes i
    ON o.id = i.id
WHERE
    type = 'U'                --user table
    AND i.IndId in (0,1)--heap, clustered index

Where are the rows stored?

If you want to dig a bit deeper and locate where particular rows are stored in the data files there are a couple of undocumented functions (2008+).

This query will return a column that contains the file number, row number and slot number separated by colons. (For this example to keep things general I’ve included all columns from the source table – you may choose to include as few or many columns as is useful)

SELECT 
sys.fn_PhysLocFormatter (%%physloc%%) AS N'File:Page:Slot'
, * 
FROM <Schema>.<Table>;

The function sys.fn_PhysLocCracker returns the same information but is used slightly differently and will return the file, page and slot numbers as separate columns.

SELECT 
files.file_id
,files.page_id
,files.slot_id
, source.*
FROM <Schema>.<Table> as source 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as files