Counting rows in an SQL table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to count rows in each of the tables in an SQL DB. I tried the
following ExecuteScalar command:

"Select Count(*) from " & mTableName

The problem is that one of the tables is huge and I'm getting a time out.

I thought I could do this with a stored procedure using the TableName as a
parmeter, but I couldn't get this to work. I thought that I could somehow
use the metadata (maybe from GetSchema) to just ask for the row count. I
couldn't figure that out either.

Can anyone help me out with this?

Art
 
Art,

Try the following query.

SELECT
rowcnt
FROM
sysindexes
WHERE
indid <= 1
and [id] = OBJECT_ID('YourTableNameGoesHere')

I don't believe the value you get back is guarenteed to be accurate
though. You may have to execute DBCC UPDATEUSAGE to get the value to
match a SELECT COUNT(*) statement.

You'd get a better response if you posted this question in a SQL Server
group instead.

Brian
 
Brian,

Thanks -- I'll give it a try. The reason I didn't post this in a SQL Server
forum is that I thought that the answer might be a .net ADO method.

For my current purpose I just need orders of magnitude of the tables, so
that if your method gives me close answers, that will be good enough.

Thanks again.

Brian Gideon said:
Art,

Try the following query.

SELECT
rowcnt
FROM
sysindexes
WHERE
indid <= 1
and [id] = OBJECT_ID('YourTableNameGoesHere')

I don't believe the value you get back is guarenteed to be accurate
though. You may have to execute DBCC UPDATEUSAGE to get the value to
match a SELECT COUNT(*) statement.

You'd get a better response if you posted this question in a SQL Server
group instead.

Brian

Hi,

I'm trying to count rows in each of the tables in an SQL DB. I tried the
following ExecuteScalar command:

"Select Count(*) from " & mTableName

The problem is that one of the tables is huge and I'm getting a time out.

I thought I could do this with a stored procedure using the TableName as a
parmeter, but I couldn't get this to work. I thought that I could somehow
use the metadata (maybe from GetSchema) to just ask for the row count. I
couldn't figure that out either.

Can anyone help me out with this?

Art
Click to expand...
Click to expand...
 
Brian,

I just wanted to let you know that I tried it and it appears to work great!
Thanks again.

Art
 
Art said:
I just wanted to let you know that I tried it and it appears to work
great! Thanks again.
Click to expand...

Although you've found a suitable method, it might be worth pointing out that
you don't have to retrieve everything from the database to count it the
rows: count(1) gives the same answer as count(*).

Andrew
 
Andrew,

SQL Server generates the exact same execution plan for either one. At
least it did for me and I tried it on a dozen tables each with
different kinds of indexes. It was always doing an index or table
scan.

Brian
 
Andrew said:
Although you've found a suitable method, it might be worth pointing
out that you don't have to retrieve everything from the database to
count it the rows: count(1) gives the same answer as count(*).
Click to expand...

Hmmm... that may not be at all useful. I tried Query Analyzer with both
methods with "set statistics io on" and there didn't seem to be any
difference.

Andrew
 
Brian said:
Andrew,

SQL Server generates the exact same execution plan for either one. At
least it did for me and I tried it on a dozen tables each with
different kinds of indexes. It was always doing an index or table
scan.
Click to expand...

Agreed. I can't remember exactly where I read about using count(1) instead
of count(*), and I was unfortunate to find it rather than an article saying
"don't bother". Apparently it's written in quite a lot of places. I, for
one, won't be writing it again.

Andrew
 
Back
Top