|
Rowcount from all tables in database
Posted: 31 Aug 2006 07:05 PM |
I can run,
[b]select rowcnt from sysindexes where indid in(0,1)[/b]
and it dutifully returns the row counts from somewhere, because sysindexes is one of those hidden backward compatibility things that you aren't meant to use.
Short of looping through a list of tables, where should I be getting the rowcounts from?
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
[url]http://philcart.blogspot.com/[/url] |
|
 |
|
 mr_fj
|
 |
| Joined: 30 Jan 2007 |
| Total Posts: 2 |
| |
|
Re: Rowcount from all tables in database
Posted: 30 Jan 2007 04:48 PM |
Hi Phil,
SysIndexes works when you put an index to a table.
Also, you need to update the statistics when ever you insert data into a table to get reflected in the index table.
You can use following query to get the row-count:
SELECT
'SELECT COUNT(*) AS [RowCount] FROM ' + so.Name
FROM
SysObjects so
WHERE
so.xType = 'U'
Only issue is that you have to run this in the SQL Query Analyser to run the output.
Let me know if this solves your problem.
Cheers,
Fred
|
|
|
|
|
|
Re: Rowcount from all tables in database
Posted: 30 Jan 2007 06:36 PM |
Thanks Fred
Although as my original post indicated, I was after a more supported method. Something along the lines of a DMV or INFORMATION_SCHEMA view.
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
[url]http://philcart.blogspot.com/[/url] |
|
|
|
urid
|
 |
| Joined: 18 Jun 2007 |
| Total Posts: 34 |
| |
|
Re: Rowcount from all tables in database
Posted: 18 Jun 2007 05:46 PM |
Hi
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; |
Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
|
|
|
|
|
Re: Rowcount from all tables in database
Posted: 18 Jun 2007 07:15 PM |
Hi Uri
Thanks for dropping by & helping out with our forums - its appreciated very much
Regards,
Greg Linwood
|
|
|
|
urid
|
|
| Joined: 18 Jun 2007 |
| Total Posts: 34 |
| |
|
Re: Rowcount from all tables in database
Posted: 18 Jun 2007 08:42 PM |
Hi Greg.
I hope you are doing well. No problem , I'm loving it.
Will be waiting for new artciles at your blog...
|
Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
|
|
|
|
pleitch
|
| Joined: 14 Aug 2007 |
| Total Posts: 1 |
| |
|
Re: Rowcount from all tables in database
Posted: 14 Aug 2007 03:12 PM |
Also - Using sysindexes is only an approximate. At TechEd they outlined that under 2008 it will contain an accurate count.
Until then there is nothing more accurate than "looping through a list of tables". It can be a little faster if you use "with (nolock)" on the tables, but that will be at the price of accuracy.
|
|
|
|
|
|
Re: Rowcount from all tables in database
Posted: 08 Oct 2007 11:00 AM |
Have a look at this page with the address below:
http://www.mountainman.com.au/software/Winluck/row_counter.html
I have been using an automated row-counter for ages.
It can be configured to alert on the condition of ROWCOUNT LOSS
is a series of designated master tables, when as a business rule,
one might never expect row-loss to occur in such tables.
Here is the code to create the stored procedure.
Simply schedule the stored procedure on a daily basis.
The rowcounter simply writes a daily tally to a history file.
Another application that can be leveraged easily from this
data is the historical growth of databases, database tables,
indexes, which is useful for planning, at times.
CREATE procedure ls_rowcounter
as
--create table rowcounts ( --
insert into rowcounts
select
getdate(),
substring(o.name,1,50),
max(i.rows),
max(refdate)
from Inpro..sysobjects o
join Inpro..sysindexes i on (o.id=i.id)
where o.type='U'
group by substring(o.name,1,50)
order by 3 desc
Best wishes,
Pete Brown
www.mountainman.com.au/software
|
|
|
|
|