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