If you want to know the number of rows in a table, the standard method is to use the following statement:
SELECT COUNT(*) FROM myTable
MySQL has some clever tricks to handle this query without visiting the table or indexes. SQL Server however does not.
This following SQL statement (for use on SQL Server only) takes advantage of data held in the system tables of your database.
-- how many rows in a specific table
SELECT rowcnt AS [RowCount]
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
AND OBJECT_NAME(id) = 'myTable'
Applying this same principle, a single query can be generated to produce a list of all tables in a database along with the number of rows in each table.
-- produce list of tables and how many rows in each
SELECT OBJECT_NAME(id) AS [TableName], rowcnt AS [RowCount]
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
If your application requires deadly accuracy, then you must use COUNT(*), its the only way to be sure.
If your tables contains millions of rows, then what's a few thousand between friends? (Thanks Gary!)
