Re: Counting rows in an SQL table

Tech-Archive recommends: Speed Up your PC by fixing your registry

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


Art wrote:
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

.

Relevant Pages

  • Re: Code behind SP_HELPINDEX / using stored procedure as sub query
    ... Index name is in sysindexes: ... indid as IndexID, ... name as IndexName ... >Where can I found index_name and index_keys of indexes in SQL Server? ...
    (microsoft.public.sqlserver.clients)
  • Re: tables without clustered index in a database
    ... SELECT object_nameFROM sysindexes WHERE indid = 0 ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • RE: Documenting Tables and Queries
    ... SQL Server does not provide a stored procedure ... If you want the code of a stored procedure, you could get the script from ... SELECT name FROM sysindexes WHERE indid in( ...
    (microsoft.public.sqlserver.tools)
  • Re: *_WA_Sys_* indexes in SysIndexes
    ... indid BETWEEN 1 AND 254 AND ... > In a solution I am currently working on, using SysIndexes. ... > files are created by SQL Server itself for optimization. ... > query, I want add a condition in "Where" clause to omit these indexes, is ...
    (microsoft.public.sqlserver.server)
  • Re: Orphan Indexes?
    ... Consider yourself lucky that you didn't work out how to delete the rows from sysindexes. ... "If it bleeds, we can fix it". ... is a bug in SQL Server, MS can probably rather quickly find a fix if you open a case... ... I have tried changing the owner of the table and then performing ...
    (microsoft.public.sqlserver.programming)