posted on Wednesday, December 21, 2005 11:59 AM
by
bknight
Quickly Retrieving the Row Count for All Tables
My typical query I run for retrieiving a list of tables and how many records in each table there are is to use the sp_msforeach stored procedure. The sproc loops through each table in a database and performs an action. Given that, you can run a query like this to obtain a list of tables and how many records are in each table:
sp_msforeachtable
"Print '?' select count(*) from ?"
The problem for me with this above query is that it's not sortable without some additional customization. So, I needed a different answer. The sysindexes table holds some very valuable information for a developer or DBA. The table shows you a record for each index in your database as well as the number of rows that are estimated to be in the table. It's not guaranteed to be up to date as far as the number of rows but it's a good number to start with. To model a query at that table, you can use the following syntax:
select
convert(varchar(30),object_name(id)) [Table Name], rows from sysindexes
where object_name(id) not like 'sys%' and indid = 1
order by object_name(id)
Instantly you retreive in a single recordset the number of estimated records in each table. The key thing to keep in mind with the above query is I'm weeding out any table that began with sys. If you have a user table that begins with sys, it will be excluded. The indid=1 part of the syntax retrieves only the primary index. There are flaws to this method because it's not exact but it does give you a rought idea of your system information.
-- Brian Knight