Record Count of all Tables

One of the simplest things that will come up constantly is trying to understand where your data is in your DB. And while not the MOST accurate way to determine this, one quick and dirty method is to get a record count from all tables in a DB.
With large datasets and a large number of tables, running SELECT Count(*) on each of the tables becomes time consuming and tedious, but by using the system tables you can quickly and easily get the record count of all tables in your DB.
The code:
SELECT ‘Owner’=convert(char(10),t.TABLE_SCHEMA),
‘Table Name’=convert(char(25),t.TABLE_NAME),
‘Record Count’=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
AND t.TABLE_TYPE = ‘BASE TABLE’
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

2 Responses to Record Count of all Tables

  1. johnstevens says:

    п»ї
    Great its very interesting subject thank you and we wait for more

  2. bgvo says:

    п»ї
    Thanks post and site very nice…. very interesting.
    I think It’s a good way to know how the tools can work.
    Thanks for sharng. Good Luck.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s