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 t.name as TableName
     , i.rows as Records
    from     sysobjects as t
        JOIN sysindexes as i
            on i.id = t.id
    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