Wednesday, January 23, 2013

Get a record or rowcount for all tables in a database

Received a request for a list of our tables and how many records each table had in it. 

In a situation like this you would want to avoid using the following logic as it actually goes through and counts each individual row.

SELECT count(*) FROM TableA

Instead using the system tables you can get an accurate and extremely fast list:

select as TableName
     , i.rows as Records
    from     sysobjects as t
        JOIN sysindexes as i
            on =
    where t.xtype = 'U' and i.indid in (0,1)
order by TableName

table row count select statement for SQL Server

This query works for SQL Server 2000 and greater.

No comments:

Post a Comment