Quick tip: Count table rows in a database, and locate those rows in the data files.

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>
	DB_NAME() as 'Database',
	SCHEMA_NAME(o.uid) as 'Schema',
    o.Name as 'Table'
    , i.Rows
    sysobjects o
    INNER JOIN sysindexes i
    ON o.id = i.id
    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)

	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.

	, source.*
FROM <Schema>.<Table> as source 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as files
This entry was posted in Database options, Database Theory, SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s