How to list down all the tables and its row count in SQL Server:
SQL Server will hold all the tables and its row count in a special table named sysindexes.
Below is the query to fetch all the information,
select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows from sysindexes a inner join sysobjects b on a.id = b.id
To list out the information for a particular database, you can use the below query.
select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows
from sysindexes a inner join sysobjects b on a.id = b.id
inner join INFORMATION_SCHEMA.TABLES c on c.[TABLE_NAME]=convert(varchar(30),object_name(a.id))
where c.Table_catalog='Database Name'
Happy Learning!!!
Regards,
Venkatesan Prabu .J
Very good work Venkatesan...
ReplyDeleteI have to build a report for my client that does a row count for all tables in all databases, so instead of running this manually for each databases, is there a way to do this with one routine?
Paul
Venkat,
ReplyDeleteThe query to display the record counts for all tables returns duplicate table names, one with 0 records and another with actual count.
Excellent Work. Works Perfect.
ReplyDeleteThanks buddy.
And idea on the duplication of tables (as mentioned by 'anonymous')?
ReplyDeleteRich
Just what I needed. Thank you!
ReplyDeleteWorks perfect, thanks buddy.
ReplyDeleteThe only stmt missing is that the query has to be run on the database where the row count information is being requested; running it from master will produce a different dataset than running it from AdventureWorks.
ReplyDeleteThe second query, requesting the catalog or database name does not work across DBs.
solution for the duplicate 0-records:
ReplyDeleteadd "where a.name like 'PK_%'" at the end of the query.
this works if each table ha a self-generated primary key.