How to count rows ?

Hello,
i'm (sql programming newbie) goin' crazy with that:
I have several tables with a lot (10000 and more) of rows on a SQL2000
server. Before i do a select or anything else on the tables i wanna return
the number of rows via stored procedure or a function.
How can i simply count the number of rows of a table WITHOUT doing a select
or whatever before i can count the rows. In online-help i read abaout a
propert called ROWS, but how to use that ?
Any idea ?
Regards RalfWhat about SELECT COUNT(*) FROM myTable in the stored procedure you mention?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Ralf Pelzl" <ralf.pelzl@.inf.hs-anhalt.de> wrote in message
news:#uyZmzNVGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hello,
> i'm (sql programming newbie) goin' crazy with that:
> I have several tables with a lot (10000 and more) of rows on a SQL2000
> server. Before i do a select or anything else on the tables i wanna return
> the number of rows via stored procedure or a function.
> How can i simply count the number of rows of a table WITHOUT doing a
select
> or whatever before i can count the rows. In online-help i read abaout a
> propert called ROWS, but how to use that ?
> Any idea ?
> Regards Ralf
>|||I have seen in the past that there is a way to get the full table count
out of the master database, thus being much quicker than actually
counting the whole table. However not only is that not a great idea for
a newbie to be trying, but also I can't remember how to do it.
Out of interest, why do you need to count your tablerows before you do
anything with them?|||> How can i simply count the number of rows of a table WITHOUT doing a
> select
You will need to perform a select of some kind.
You can try
select rowcnt from sysindexes where id = OBJECT_ID('tablename') and indid in
(0,1)
However, this will depend on current sysindexes data (e.g. by running DBCC
UPDATEUSAGE). If you don't update usage first, then hopefully you can rely
on potentially inaccurate numbers. Otherwise, SELECT COUNT(*) FROM
tablename is the only accurate way.
A|||Hello
thanks for the quick answer. The reason to read out the rowcount is that i
write a .NET-Application where i read parts of datarows into the
application, work with it and update it in the database. This progress will
take a while so i wanna add a progressbar. The only way (i know) to display
a progressbar is to know the last record number to set the min=0 and the
max=rowcount.
Regards
Ralf
"Will" <william_pegg@.yahoo.co.uk> schrieb im Newsbeitrag
news:1143822887.413725.285180@.e56g2000cwe.googlegroups.com...
>I have seen in the past that there is a way to get the full table count
> out of the master database, thus being much quicker than actually
> counting the whole table. However not only is that not a great idea for
> a newbie to be trying, but also I can't remember how to do it.
> Out of interest, why do you need to count your tablerows before you do
> anything with them?
>|||Hello,
thanks for the answer. I want create a function i can use for more than one
table. But i dont know a way to give this function the tablename as
parameter. I tried that:
create function [dbo].[getRowCountOf] (@.tableName nvarchar(20)
returns integer
AS
return select count(*) from @.tableName
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> schrieb im
Newsbeitrag news:KoidnbNxSocEyLDZRVnysw@.bt.com...
> What about SELECT COUNT(*) FROM myTable in the stored procedure you
> mention?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Ralf Pelzl" <ralf.pelzl@.inf.hs-anhalt.de> wrote in message
> news:#uyZmzNVGHA.5148@.TK2MSFTNGP12.phx.gbl...
> select
>|||sorry, i send to fast...
sql means, that i have to declare @.tableName.
Is there a way to make the tablename variable in a function ?
Regards
Ralf
"Ralf Pelzl" <ralf.pelzl@.inf.hs-anhalt.de> schrieb im Newsbeitrag
news:udBe4lOVGHA.5004@.TK2MSFTNGP11.phx.gbl...
> Hello,
> thanks for the answer. I want create a function i can use for more than
> one table. But i dont know a way to give this function the tablename as
> parameter. I tried that:
> create function [dbo].[getRowCountOf] (@.tableName nvarchar(20)
> returns integer
> AS
> return select count(*) from @.tableName
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> schrieb im
> Newsbeitrag news:KoidnbNxSocEyLDZRVnysw@.bt.com...
>|||DECLARE @.tablename SYSNAME;
SET @.tablename = N'<enter table here>';
SELECT RowCnt FROM sysindexes WHERE id = OBJECT_ID(@.tablename) AND indid IN
(0,1);
Otherwise, see
http://www.sommarskog.se/dynamic_sql.html
"Ralf Pelzl" <ralf.pelzl@.inf.hs-anhalt.de> wrote in message
news:eD6O8nOVGHA.1868@.TK2MSFTNGP09.phx.gbl...
> sorry, i send to fast...
> sql means, that i have to declare @.tableName.
> Is there a way to make the tablename variable in a function ?
> Regards
> Ralf
> "Ralf Pelzl" <ralf.pelzl@.inf.hs-anhalt.de> schrieb im Newsbeitrag
> news:udBe4lOVGHA.5004@.TK2MSFTNGP11.phx.gbl...
>|||Hello,
thanks for answer. I tried this:
create function [dbo].[getRowCountOf] (@.tableName nvarchar(20)
returns integer
as
return select count(*) from @.tableName
But it does'nt work because sql means i have to declare @.tableName. Do You
know how i can make the tablename variable ? Otherwise i will use your hint:
create function [dbo].[getRowCountOf] (@.tableName nvarchar(20)
returns table
as
return select rowcnt from sysindexes where id = OBJECT_ID('(@.tableName') and
indid in (0,1)
I've tried it and it works :-)
Regards
Ralf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:%23b5lwGOVGHA.2704@.tk2msftngp13.phx.gbl...
> You will need to perform a select of some kind.
> You can try
> select rowcnt from sysindexes where id = OBJECT_ID('tablename') and indid
> in (0,1)
> However, this will depend on current sysindexes data (e.g. by running DBCC
> UPDATEUSAGE). If you don't update usage first, then hopefully you can
> rely on potentially inaccurate numbers. Otherwise, SELECT COUNT(*) FROM
> tablename is the only accurate way.
> A
>|||By the way, without quotas arount @.tableName
"Ralf Pelzl" <ralf.pelzl@.inf.hs-anhalt.de> schrieb im Newsbeitrag
news:elwIwxOVGHA.1572@.tk2msftngp13.phx.gbl...
> Hello,
> thanks for answer. I tried this:
> create function [dbo].[getRowCountOf] (@.tableName nvarchar(20)
> returns integer
> as
> return select count(*) from @.tableName
> But it does'nt work because sql means i have to declare @.tableName. Do You
> know how i can make the tablename variable ? Otherwise i will use your
> hint:
> create function [dbo].[getRowCountOf] (@.tableName nvarchar(20)
> returns table
> as
> return select rowcnt from sysindexes where id = OBJECT_ID('(@.tableName')
> and indid in (0,1)
> I've tried it and it works :-)
> Regards
> Ralf
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
> Newsbeitrag news:%23b5lwGOVGHA.2704@.tk2msftngp13.phx.gbl...
>