Counting records in a SQL Server table

Counting the number to records in a table is something you need to do from time to time. Especially for long running processes where you need to have some clue as to what's instore, if for no other reason than to provide feedback on progress, in terms of percentage complete.

Until this morning, I though select count(*), or select count(field_name) was my only option. That bugged me because select count(*) can't be efficient, and select count(field_name) is tricky to make generic because you don't always have a consistant field name to use. It ought to be possible to write something that simply takes a table name as a parameter and tells you how many rows are in that table. Turns out, there is!

It's explained here - http://www.sqlservercentral.com/links/894320/119638 (although you need to be a member of the site - free - to read the article I think.)

It boils down to use of a stored proc: sp_spaceused. You pass the table name and one of the rows returned by the proc is the number of rows in that table. (Just as it should be!) Nice.

The contents of the proc vary for SQL 2000 and SQL 2005/2008 but the proc is still around.