Rowcount of the biggest table
-
Hi everyone,
I'm trying to find total count of rows of the biggest table of our database but the query either runs forever without giving results or gives me incorrect count.
I'm using the following:
(1)Select count(*) from XYZ -- runs forever without giving the results
(2)sp_spaceused --- gives incorrect results
(3)select rows, rowcnt from sysindexes where name=' XYZ ' --- No results
Are there any other methods to get the rowcount?
Thanks.
-
The fastest (and most accurate) is to query sys.indexes - example:
SELECT
c .row_count AS [RowCount]
FROM
sys. objects a
JOIN
sys. indexes b
ON b .OBJECT_ID = a. OBJECT_ID
JOIN
sys. dm_db_partition_stats AS c
ON b .OBJECT_ID = c. OBJECT_ID
AND b. index_id = c .index_id
WHERE
b .index_id < 2
AND a .is_ms_shipped = 0
AND a .name = 'YourTable'I typically make this into a simple scalar function
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
MyDoggieJessie (12/19/2012)
The fastest (and most accurate) is to query sys.indexes - example:SELECT
c .row_count AS [RowCount]
FROM
sys. objects a
JOIN
sys. indexes b
ON b .OBJECT_ID = a. OBJECT_ID
JOIN
sys. dm_db_partition_stats AS c
ON b .OBJECT_ID = c. OBJECT_ID
AND b. index_id = c .index_id
WHERE
b .index_id < 2
AND a .is_ms_shipped = 0
AND a .name = 'YourTable'
Sorry forgot to mention that it's SQL 2000.
-
It's easy to forget the standard reports in SSMS.
-
Ah SQL2000. Ignore me then. That standard report won't work either.
-
This should work in SQL Server 2000 (not tested though):
select o.name, i.rowsfrom sysindexes i
join sysobjects o on i.id = o.id
where i.indid in (0, 1)
order by 2 desc
-
Richard Fryar (12/19/2012)
This should work in SQL Server 2000 (not tested though):select o.name, i.rowsfrom sysindexes i
join sysobjects o on i.id = o.id
where i.indid in (0, 1)
order by 2 desc
I tried that before but inaccurate results.
-
That can happen in SQL Server 2000.
Have you tried running DBCC UPDATEUSAGE?
-
Richard Fryar (12/19/2012)
That can happen in SQL Server 2000.Have you tried running DBCC UPDATEUSAGE?
Yeah, I think this is the only option left.
Thanks everyone.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply