Rowcounts on all tables in a DB

I need to create a query to get the row counts of all tables in a
particular database. Can somebody point me to a method of doing this?
Regards,
Randy
This will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT
|||On Feb 20, 5:23Xam, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. XCan somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx