Is there information_schema.view for count of rows in tables?

Hello,
Is there some kind of information_schema.something which lists tables and
the current count of rows in each table?
Thanks,
RichI tried something like this which did not work:
select table_name, (Select count(*) from table_name) as NumOfRows
from information_schema.tables
Any suggestions?
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||See
http://www.databasejournal.com/feat...cle.php/3441031
The above details two methods of getting the results that you want; one by
using a cursor, and another by using an undocumented stored procedure in SQL
Server 2000. Since I don't know what version you are using, I would recommen
d
looking at the cursor method.
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||Rich (Rich@.discussions.microsoft.com) writes:
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
Not in INFORMATION_SCHEMA, but in the system table sysindexes:
SELECT object_name(id), rows
FROM sysindexes
WHERE indid IN (0, 1)
I should add that the value you see here may not be exactly on the
mark, but most often it's close enough. This is a lot faster than
accessing each table.
The WHERE clause looks funny, but sysindexes is a bit special. For
each table there is always a row with indid = 0 *or* 1 - never both.
indid is one if the table has a clustered index, else it's zero.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As a follow up, you can use
SELECT object_name(i.[id]), rows
FROM sysindexes i INNER JOIN sysobjects o
ON i.[id] = o.[id]
WHERE i.indid IN (0, 1) AND o.xtype = 'U'
if you just want the row count of user-defined base tables. You can run the
following to get a comparison between counting the rows with COUNT(*) and
what appears in sysindexes. In a DB that I have, there was no difference for
any of the tables.
set nocount on
declare @.cnt int
declare @.table varchar(128)
declare @.cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @.table
while @.@.fetch_status = 0
begin
set @.cmd = 'select ''' + @.table + ''', count(*) from ' + @.table
insert into #rowcount exec (@.cmd)
fetch next from tables into @.table
end
CLOSE tables
DEALLOCATE tables
SELECT t1.tablename, t1.rowcnt AS "Counted with COUNT(*)", t2.[rows] AS
"Counted via sysindexes"
FROM #rowcount t1 INNER JOIN (
SELECT object_name(i.[id]) AS "tablename", i.[rows]
FROM sysindexes i INNER JOIN sysobjects o
ON i.[id] = o.[id]
WHERE i.indid IN (0, 1) AND o.xtype = 'U') t2
ON t1.tablename = t2.tablename
drop table #rowcount
--
"Erland Sommarskog" wrote:

> Rich (Rich@.discussions.microsoft.com) writes:
> Not in INFORMATION_SCHEMA, but in the system table sysindexes:
> SELECT object_name(id), rows
> FROM sysindexes
> WHERE indid IN (0, 1)
> I should add that the value you see here may not be exactly on the
> mark, but most often it's close enough. This is a lot faster than
> accessing each table.
> The WHERE clause looks funny, but sysindexes is a bit special. For
> each table there is always a row with indid = 0 *or* 1 - never both.
> indid is one if the table has a clustered index, else it's zero.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Thank you all for your replies. I did try both methods, the sysIndex table
method, and the cursor method (which included the sysindex table).
A few of the tables did have a slightly different count with count(*) than
the count in sysindex. I will guess that count(*) was probably a little bit
more current, as these are live tables getting data as we speak.
Does anyone know if peformance on data entry was affected while I ran the
cursor? The cursor took 25 seconds to run. Or is running this kind of
cursor pretty seamless against the data entry table?
Thanks again,
Rich
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||> I will guess that count(*) was probably a little bit more current
The count(*) rows (assuming you didn't use a nolock hint) will be almost
perfect as of the instant that the query finishes. The sysindexes value is
a reasonably recent value that is used for the optimizer to make "guesses"
as to how many rows are in the table much much faster than counting all of
the rows.

> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
Performance is affected no matter what the query :) Seriously, it is
affected in that some minor blocking will take place, and if you don't have
an index on the table, every row will have to be "touched" and locked during
the counting process. But the overhead on any other queries is likely
minimal.
The key here is to base which method you use based on your needs. If you
just want to know the number of rows in the table, then probably using
sysindexes is best, unless you need perfect results. Otherwise, locking is
fine. Note I said almost perfect in the first paragraph. Under the default
conditions in SQL Server, users can delete or insert rows that have already
been counted, so it is only as perfect as the users of the data allow.
I could keep going, but unless you really care about being perfect, it is
not really worth it :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:7C3F3BE6-296E-4F88-9C22-AB15DAA66050@.microsoft.com...
> Thank you all for your replies. I did try both methods, the sysIndex
> table
> method, and the cursor method (which included the sysindex table).
> A few of the tables did have a slightly different count with count(*) than
> the count in sysindex. I will guess that count(*) was probably a little
> bit
> more current, as these are live tables getting data as we speak.
> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
> Thanks again,
> Rich
> "Rich" wrote:
>|||Rich (Rich@.discussions.microsoft.com) writes:
> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
Unless there was a NOLOCK on the SELECT COUNT(*) queries, users could
experience blocking when you run the query. This can be particularly
noticeable on a large table that does not have any non-clustered indexes.
(If there is a non-clustered index, the COUNT(*) will run over that
index, which is cheaper than scanning the entire table.)
The query on sysindex is considerably leaner on resources, and it's
unlikely that it would cause blocking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx