Count Records in a Database

This script is used to return the number of rows in each table of a database.

The function uses sp_spaceused.

Suitable For:

2000, 2005, 2008

Sample Output:

Sample results of counting records in a database.

Download/Script:

count_records_in_database.zip

-- Count records in a database.sql
-- Copyright (C) 2010 VirtualObjectives http://www.VirtualObjectives.com.au

IF OBJECT_ID('tempdb..#tablespace') IS NOT NULL
	DROP TABLE #tablespace

CREATE TABLE #tablespace (
	TableName varchar(255),
	[RowCount] int,
	ReservedSpace varchar(20),
	Data varchar(20),
	IndexSize varchar(20),
	UnusedSpace varchar(20)
)

EXEC sp_MSForEachTable @command1="insert into #tablespace exec sp_spaceused'?'"

SELECT * FROM #tablespace
ORDER BY [RowCount] DESC

IF OBJECT_ID('tempdb..#tablespace') IS NOT NULL
	DROP TABLE #tablespace