SQL Server: List of tables and their rows counts in a database

I was trying to identify which tables were affected by an update though an application (3rd party). There were more than 300+ tables in the Database. I was hoping to avoid checking each of them for changes. To get the list of tables and their rows counts in a database you can use the following SQL query before and after the update.

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC

Note:
The sysindexes table is usually a little bit inaccurate, because it is not updated constantly. It will also include the 'dtproperties' table, which is one of those hybrid tables that falls neither under the 'system' nor 'user' category. It does not appear in Enterprise Manager's "Tables" view if you choose to hide system objects, but it shows up above.

In any case, it is generally not recommended to query against the system objects directly, so please only use the above for rough, ad-hoc guesstimates.

/Adnan




This post is from my old blog posted on February, 2011, that unfortunately no longer exist.