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

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,
RandyThis 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|||Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
likely to reflect reality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:B2937C70-6DD2-4E49-8028-1148FF13E8FF@.microsoft.com...
> 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
>|||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:23=A0am, 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. =A0Can somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx

Row Hiding in a Matrix

Hi,
I have a P&L report that I am trying to produce. Due to the many
column groups, I prefer to use the matrix method.
However, I can't seem to surpress duplicate rows that are created
because in some instances the total value of the outer row group is the
same as the total value of the inner group.
I was performing this nicely with a table by using the visibility
surpression on the entire row (which didn't get rid of the outer
group).
It seems there is no equivalence in the matrix control and when I set
the visibility of the outer group to conditionally hide none of the
inner group values seem to show (I guess this is by design)
I guess what I want to do is to surpress the visibiliy of either the
summary or the last inner group.
When I try to conditionally set the visibility of the summary, the
label is not shown but the total value is.
Either method seems to have a limitation.
Any ideas?
ThanksI tried Hide Duplicates, too, but that doesn't do anything even though
the visible fields of the two consecutive rows have identical values.