SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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


Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...