MS SQL Server - Total rows in a table

Author

Total rows in a table

John J. Hughes II

2005-07-18, 1:23 pm

I need to know how many rows are in a table. The most obvious solution is
to issue a count queue "select count(*) from tbl1" but on large tables this
can be very slow, in the several minute catagory. I was wondering if there
was a better way. I know if I right click a table in Enterpise Manager and
select properties the row count is very quick so I am thinking there might
be a better way.

regards,
John

Neil MacMurchy

2005-07-18, 1:23 pm

did you hear "John J. Hughes II" <no@invalid.com> say in
news:Ou37g87iFHA.1968@TK2MSFTNGP14.phx.gbl:

> I need to know how many rows are in a table. The most obvious
> solution is to issue a count queue "select count(*) from tbl1" but on
> large tables this can be very slow, in the several minute catagory. I
> was wondering if there was a better way. I know if I right click a
> table in Enterpise Manager and select properties the row count is very
> quick so I am thinking there might be a better way.
>
> regards,
> John
>
>
>

sp_table_validation 'tablename'

--
Neil MacMurchy

http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

Tibor Karaszi

2005-07-18, 1:23 pm

You can get the information from sysindexes. It is not reliable, however (and subsequentially, EM
can be wrong).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"John J. Hughes II" <no@invalid.com> wrote in message news:Ou37g87iFHA.1968@TK2MSFTNGP14.phx.gbl...
>I need to know how many rows are in a table. The most obvious solution is to issue a count queue
>"select count(*) from tbl1" but on large tables this can be very slow, in the several minute
>catagory. I was wondering if there was a better way. I know if I right click a table in Enterpise
>Manager and select properties the row count is very quick so I am thinking there might be a better
>way.
>
> regards,
> John
>

Alejandro Mesa

2005-07-18, 1:23 pm

Try,

use northwind
go

dbcc updateusage('northwi
nd', 'dbo.order details') WITH NO_INFOMSGS
go

select
object_name([id]) as table_name,
convert(int, rowcnt) as [rows]
from
dbo.sysindexes
where
indid < 2 and [id] = object_id('dbo.order details')
go

AMB

"John J. Hughes II" wrote:

> I need to know how many rows are in a table. The most obvious solution is
> to issue a count queue "select count(*) from tbl1" but on large tables this
> can be very slow, in the several minute catagory. I was wondering if there
> was a better way. I know if I right click a table in Enterpise Manager and
> select properties the row count is very quick so I am thinking there might
> be a better way.
>
> regards,
> John
>
>
>

John J. Hughes II

2005-07-18, 1:23 pm

Thanks

Neil MacMurchy

2005-07-18, 8:23 pm

did you hear "John J. Hughes II" <no@invalid.com> say in
news:eHGLoc8iFHA.1048@tk2msftngp13.phx.gbl:

> Thanks
>
>

you could try this as well:

select b.name AS TableName, a.rows
from sysindexes a join sysobjects b ON a.id=b.id
where b.name = 'tableyouwant' and a.status = 0

but as Tibor mentioned this is sometimes out of whack with reality. Count
is more acurate, but slower.

--
Neil MacMurchy

http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

Hari Prasad

2005-07-18, 8:23 pm

Hi,

You could also use the below system procedure:-

use dbname
go
sp_spaceused <tablename> ,@updateusage='true_
_'

Execute the above command when database have very less access. Because the
above command internally execute
DBCC UPDATEUSAGE on the table with Shared locks.

Thanks
Hari
SQL Server MVP

"Alejandro Mesa" < AlejandroMesa@discus
sions.microsoft.com> wrote in message
news:44926259-3444-478B-A3A7- 982593460E67@microso
ft.com...[color=darkred]
> Try,
>
> use northwind
> go
>
> dbcc updateusage('northwi
nd', 'dbo.order details') WITH NO_INFOMSGS
> go
>
> select
> object_name([id]) as table_name,
> convert(int, rowcnt) as [rows]
> from
> dbo.sysindexes
> where
> indid < 2 and [id] = object_id('dbo.order details')
> go
>
>
> AMB
>
> "John J. Hughes II" wrote:
>

Sponsored Links