March 31, 2004 at 1:09 pm
silly question, but is there a system function or so to return the number of rows in a table, instead of writing UDF with "select count(*) from ..."?
thanks
March 31, 2004 at 1:16 pm
i don't know of any other way ....
cheers
dbgeezer
March 31, 2004 at 1:46 pm
How do you want to see the information?
You could use Enterprise Manager, expand down to the database, expand the database, click on Tables, and in the right pane, right click on the table and select Properties. That will show you how many rows there are.
-SQLBill
March 31, 2004 at 1:53 pm
You can query the rows column on the sysindexes table.
Should be updated frecuently but i'm not use of how accurate it can be.
You can try comparing those values against select count(*).
March 31, 2004 at 1:56 pm
Just found this in "Inside Microsoft SQL Server 2000" by Kalen Delaney...
SELECT rows
FROM sysindexes
WHERE id=OBJECT_ID ('dbname')
AND indid < 2
This only works for base tables, not views and only if you apply no selection criteria via a WHERE clause.
rows column in the sysindexes table keeps the current rowcount dynamically for the clustered index. If one exists, indid = 1. If no clustered index exists, sysindexes keeps the count for the table (indid = 0).
According to the author, the above is "... a considerably faster way ..." than COUNT(*).
-SQLBill
editted to fix script. The line WHERE id+OBJECT_ID ('dbname') was fixed to replace the + with an = sign.
March 31, 2004 at 2:01 pm
thanks, "rows" column of sysindexes is what I am using now.
I just noticed that the value of "rows" different type of indexes, say idind=1 (cluster) or 2 (noncluster), for the same table, sometimes are slightly different. don't know why.
April 1, 2004 at 7:41 am
Much easier way is to run sp_mstablespace tablename.
This will return number of rows, size, index size, etc...
April 1, 2004 at 9:15 am
you can also run
sp_spaceused tablename
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
April 1, 2004 at 10:54 am
if you use sp_spaceused tablename then it is not always accurate you have to run (especially about the space being used)
sp_spaceused tablename @updateusage = 'TRUE'
and it takes a bit longer. See BOL for more details.
April 1, 2004 at 11:19 am
thanks David. I thought Raymond only wanted rowcount. The only time that I've found sp_spaceused to not report the number of rows was if someone was in a transaction. Thanks for the update.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
April 1, 2004 at 3:32 pm
A variation on above sysindex posts:
Select so.name, sc.rowcnt as RowsInTable
From sysobjects so (nolock)
JOIN sysindexes sc (nolock) on so.id = sc.id
WHERE sc.indid < 2
order by RowsInTable desc
I have found this to be accurate enough for most counts. If you absolutely must have the exact number of rows in a heavily used OLTP table don't use this (and good luck!); otherwise, shouldn't be a problem. For example:
Select sum(sc.rowcnt)
From sysindexes sc
WHERE sc.indid < 2
instantly shows me app. how many rows are in the entire database. It would take forever to do this with count(*), and would probably be even less accurate, as the counts will change between the start and finish of the batch.
cl
Signature is NULL
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply