Record Count of all Tables
December 31, 2007One 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
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
п»ї
Great its very interesting subject thank you and we wait for more
п»ї
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.