Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

using cursor to get a row count for all user tables...

17 posts in General Discussion Last posting was on 2010-05-26 18:09:53.0Z
Lisa Chow Posted on 2010-05-23 11:02:46.0Z
Sender: 3221.4bf8e7ff.1804289383@sybase.com
From: Lisa Chow
Newsgroups: sybase.public.ase.general
Subject: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bf90b56.3b69.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 May 2010 04:02:46 -0700
X-Trace: forums-1-dub 1274612566 10.22.241.41 (23 May 2010 04:02:46 -0700)
X-Original-Trace: 23 May 2010 04:02:46 -0700, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29239
Article PK: 78473

Please help me troubleshoot the below code. I'm trying to
convert it over from MS SQL Server to Sybase. The MS SQL
Server code is very similar to Sybase ASE, but different in
places. I'm hoping that the below code is correct and will
work on ASE, but was hoping to get your feedback. Is there
a better way to do this?

------------------------------------------------

declare table_cursor cursor
for select name from sysobjects where type='U'
for read only

declare @table_name sysname

set @total_recs = 0

open table_cursor
fetch table_cursor into @table_name
while @@sqlstatus = 0
begin

print @tablename + ' - '
select count(*) from @table_name

fetch table_cursor into @table_name
end
close table_cursor
deallocate cursor table_cursor

------------------------------------------------

Thank you

Lisa


Carl Kayser Posted on 2010-05-23 14:23:28.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4bf90b56.3b69.1681692777@sybase.com>
Subject: Re: using cursor to get a row count for all user tables...
Lines: 64
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bf93a60@forums-1-dub>
Date: 23 May 2010 07:23:28 -0700
X-Trace: forums-1-dub 1274624608 10.22.241.152 (23 May 2010 07:23:28 -0700)
X-Original-Trace: 23 May 2010 07:23:28 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29243
Article PK: 78475


<Lisa Chow> wrote in message news:4bf90b56.3b69.1681692777@sybase.com...
> Please help me troubleshoot the below code. I'm trying to
> convert it over from MS SQL Server to Sybase. The MS SQL
> Server code is very similar to Sybase ASE, but different in
> places. I'm hoping that the below code is correct and will
> work on ASE, but was hoping to get your feedback. Is there
> a better way to do this?
>
> ------------------------------------------------
>
> declare table_cursor cursor
> for select name from sysobjects where type='U'
> for read only
>
> declare @table_name sysname
>
> set @total_recs = 0
>
> open table_cursor
> fetch table_cursor into @table_name
> while @@sqlstatus = 0
> begin
>
> print @tablename + ' - '
> select count(*) from @table_name
>
> fetch table_cursor into @table_name
> end
> close table_cursor
> deallocate cursor table_cursor
>
> ------------------------------------------------
>
> Thank you
>
> Lisa

Which ASE version? Is there a better way? "Better" is hard to quantify for
this situation and I believe that this issue is in older threads.

For ASE 15.x

select 'Table' = name, 'Rows' = row_count (db_id (), id)
from sysobjects
where type ='U'
order by 'Table'

For 12.5.x there would be a join with sysindexes (join on id, indid=0) to
acquire rowcnt (sysindexes.doampg).

The issue is that row_count () and rowcnt are defined as approximate values.
It seems that these approximations are more real-time and accurate than in
the past. (It might be that ASE doumentation references to estimates is now
closer to being CYA for exceptional situations. Has anyone else had recent
experience with the (non)consistency of the two techniques?) The above will
certainly be faster than using a cursor and table or index scans of every
user table.

The argument for the cursor/scan approach is that it is 100% accurate and
not estimates. But if the tables are very active with deletes and inserts
then when your report finishes it will be out of date!


Lisa Chow Posted on 2010-05-25 05:31:55.0Z
Sender: 76cf.4bfb5fee.1804289383@sybase.com
From: Lisa Chow
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bfb60cb.76f1.1681692777@sybase.com>
References: <4bf93a60@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 May 2010 22:31:55 -0700
X-Trace: forums-1-dub 1274765515 10.22.241.41 (24 May 2010 22:31:55 -0700)
X-Original-Trace: 24 May 2010 22:31:55 -0700, 10.22.241.41
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29248
Article PK: 78482

Hi,

I'm using both ASE 12.5.4 and ASE 15.0.3. I tried the below
sql statement for ASE 12.5.4, but seems to be incorrect.

select sysobjects.name, rowcnt(sysindexes.doampg)
from sysobjects inner join sysindexes on sysobjects.id =
sysindexes.id
where sysobjects.type = 'U'
order by sysobjects.name


What are your thoughts?

> <Lisa Chow> wrote in message
> > news:4bf90b56.3b69.1681692777@sybase.com... Please help
> > me troubleshoot the below code. I'm trying to convert
> > it over from MS SQL Server to Sybase. The MS SQL Server
> > code is very similar to Sybase ASE, but different in
> places. I'm hoping that the below code is correct and
> > will work on ASE, but was hoping to get your feedback.
> > Is there a better way to do this?
> >
> > ------------------------------------------------
> >
> > declare table_cursor cursor
> > for select name from sysobjects where type='U'
> > for read only
> >
> > declare @table_name sysname
> >
> > set @total_recs = 0
> >
> > open table_cursor
> > fetch table_cursor into @table_name
> > while @@sqlstatus = 0
> > begin
> >
> > print @tablename + ' - '
> > select count(*) from @table_name
> >
> > fetch table_cursor into @table_name
> > end
> > close table_cursor
> > deallocate cursor table_cursor
> >
> > ------------------------------------------------
> >
> > Thank you
> >
> > Lisa
>
> Which ASE version? Is there a better way? "Better" is
> hard to quantify for this situation and I believe that
> this issue is in older threads.
>
> For ASE 15.x
>
> select 'Table' = name, 'Rows' = row_count (db_id (), id)
> from sysobjects
> where type ='U'
> order by 'Table'
>
> For 12.5.x there would be a join with sysindexes (join on
> id, indid=0) to acquire rowcnt (sysindexes.doampg).
>
> The issue is that row_count () and rowcnt are defined as
> approximate values. It seems that these approximations
> are more real-time and accurate than in the past. (It
> might be that ASE doumentation references to estimates is
> now closer to being CYA for exceptional situations. Has
> anyone else had recent experience with the
> (non)consistency of the two techniques?) The above will
> certainly be faster than using a cursor and table or index
> scans of every user table.
>
> The argument for the cursor/scan approach is that it is
> 100% accurate and not estimates. But if the tables are
> very active with deletes and inserts then when your
> report finishes it will be out of date!
>
>


Lisa Chow Posted on 2010-05-25 06:56:22.0Z
Sender: 76cf.4bfb5fee.1804289383@sybase.com
From: Lisa Chow
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bfb7496.79eb.1681692777@sybase.com>
References: <4bf93a60@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 May 2010 23:56:22 -0700
X-Trace: forums-1-dub 1274770582 10.22.241.41 (24 May 2010 23:56:22 -0700)
X-Original-Trace: 24 May 2010 23:56:22 -0700, 10.22.241.41
Lines: 86
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29249
Article PK: 78483

Hi,

I ended up doing the following and seems to be working (in
ASE 12.5.4)...

select name, rowcnt(sysindexes.doampg)
from sysindexes
where name in
(select name from sysobjects where type = "U")

Thank you for your help!

P.S. Still puzzled why my cursor code below isn't working.
It would be nice to do the cursor approach still. Since
they want to compare exact table row count. Can you please
advise on what might be missing. Thx

> <Lisa Chow> wrote in message
> > news:4bf90b56.3b69.1681692777@sybase.com... Please help
> > me troubleshoot the below code. I'm trying to convert
> > it over from MS SQL Server to Sybase. The MS SQL Server
> > code is very similar to Sybase ASE, but different in
> places. I'm hoping that the below code is correct and
> > will work on ASE, but was hoping to get your feedback.
> > Is there a better way to do this?
> >
> > ------------------------------------------------
> >
> > declare table_cursor cursor
> > for select name from sysobjects where type='U'
> > for read only
> >
> > declare @table_name sysname
> >
> > set @total_recs = 0
> >
> > open table_cursor
> > fetch table_cursor into @table_name
> > while @@sqlstatus = 0
> > begin
> >
> > print @tablename + ' - '
> > select count(*) from @table_name
> >
> > fetch table_cursor into @table_name
> > end
> > close table_cursor
> > deallocate cursor table_cursor
> >
> > ------------------------------------------------
> >
> > Thank you
> >
> > Lisa
>
> Which ASE version? Is there a better way? "Better" is
> hard to quantify for this situation and I believe that
> this issue is in older threads.
>
> For ASE 15.x
>
> select 'Table' = name, 'Rows' = row_count (db_id (), id)
> from sysobjects
> where type ='U'
> order by 'Table'
>
> For 12.5.x there would be a join with sysindexes (join on
> id, indid=0) to acquire rowcnt (sysindexes.doampg).
>
> The issue is that row_count () and rowcnt are defined as
> approximate values. It seems that these approximations
> are more real-time and accurate than in the past. (It
> might be that ASE doumentation references to estimates is
> now closer to being CYA for exceptional situations. Has
> anyone else had recent experience with the
> (non)consistency of the two techniques?) The above will
> certainly be faster than using a cursor and table or index
> scans of every user table.
>
> The argument for the cursor/scan approach is that it is
> 100% accurate and not estimates. But if the tables are
> very active with deletes and inserts then when your
> report finishes it will be out of date!
>
>


Lisa Chow Posted on 2010-05-25 08:25:29.0Z
Sender: 7c70.4bfb8858.1804289383@sybase.com
From: Lisa Chow
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bfb8979.7c8b.1681692777@sybase.com>
References: <4bf93a60@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 May 2010 01:25:29 -0700
X-Trace: forums-1-dub 1274775929 10.22.241.41 (25 May 2010 01:25:29 -0700)
X-Original-Trace: 25 May 2010 01:25:29 -0700, 10.22.241.41
Lines: 102
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29250
Article PK: 78484

Hi,

I'm using the below SQL statements...


------------------------
For ASE 12.5.4
------------------------
select name, rowcnt(sysindexes.doampg) from sysindexes where
name in (select name from sysobjects where type = 'U') order
by name

go



------------------------
For ASE 15.0.3
------------------------
select name, row_count(db_id(), id) from sysobjects where
type = 'U' order by name

go


Is the above syntax correct?

Will the 'order by' statment cuase issues (using with
aggregate functions)?


Thank you

> <Lisa Chow> wrote in message
> > news:4bf90b56.3b69.1681692777@sybase.com... Please help
> > me troubleshoot the below code. I'm trying to convert
> > it over from MS SQL Server to Sybase. The MS SQL Server
> > code is very similar to Sybase ASE, but different in
> places. I'm hoping that the below code is correct and
> > will work on ASE, but was hoping to get your feedback.
> > Is there a better way to do this?
> >
> > ------------------------------------------------
> >
> > declare table_cursor cursor
> > for select name from sysobjects where type='U'
> > for read only
> >
> > declare @table_name sysname
> >
> > set @total_recs = 0
> >
> > open table_cursor
> > fetch table_cursor into @table_name
> > while @@sqlstatus = 0
> > begin
> >
> > print @tablename + ' - '
> > select count(*) from @table_name
> >
> > fetch table_cursor into @table_name
> > end
> > close table_cursor
> > deallocate cursor table_cursor
> >
> > ------------------------------------------------
> >
> > Thank you
> >
> > Lisa
>
> Which ASE version? Is there a better way? "Better" is
> hard to quantify for this situation and I believe that
> this issue is in older threads.
>
> For ASE 15.x
>
> select 'Table' = name, 'Rows' = row_count (db_id (), id)
> from sysobjects
> where type ='U'
> order by 'Table'
>
> For 12.5.x there would be a join with sysindexes (join on
> id, indid=0) to acquire rowcnt (sysindexes.doampg).
>
> The issue is that row_count () and rowcnt are defined as
> approximate values. It seems that these approximations
> are more real-time and accurate than in the past. (It
> might be that ASE doumentation references to estimates is
> now closer to being CYA for exceptional situations. Has
> anyone else had recent experience with the
> (non)consistency of the two techniques?) The above will
> certainly be faster than using a cursor and table or index
> scans of every user table.
>
> The argument for the cursor/scan approach is that it is
> 100% accurate and not estimates. But if the tables are
> very active with deletes and inserts then when your
> report finishes it will be out of date!
>
>


Carl Kayser Posted on 2010-05-25 10:24:26.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4bf93a60@forums-1-dub> <4bfb8979.7c8b.1681692777@sybase.com>
Subject: Re: using cursor to get a row count for all user tables...
Lines: 32
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bfba55a$1@forums-1-dub>
Date: 25 May 2010 03:24:26 -0700
X-Trace: forums-1-dub 1274783066 10.22.241.152 (25 May 2010 03:24:26 -0700)
X-Original-Trace: 25 May 2010 03:24:26 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29251
Article PK: 78489


<Lisa Chow> wrote in message news:4bfb8979.7c8b.1681692777@sybase.com...
> Hi,
>
> I'm using the below SQL statements...
>
>
> ------------------------
> For ASE 12.5.4
> ------------------------
> select name, rowcnt(sysindexes.doampg) from sysindexes where
> name in (select name from sysobjects where type = 'U') order
> by name
>
> go
>

select 'Table' = o.name, 'Rows' = rowcnt (i.sysindxes.doampg)
from sysinobjects o,
sysindexes i
where o.type = 'U'
and i.id = o.id
and i.indid < 2

The PK for sysindexes is (id, indid) and the "base table information" is in
indid 0 or 1. Only one of these will exist for a table. If the table has
an APL clustered index then the "base info" will be stored in indid 1. If
the table does not have an APL clustered index then the "base info" will be
with indid 0. Note that you do not want to use "name" for the join since
there could be same-named tables with different owners.


Carl Kayser Posted on 2010-05-26 11:25:53.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4bfba55a$1@forums-1-dub> <4bfcd1a4.56c5.1681692777@sybase.com>
Subject: Re: using cursor to get a row count for all user tables...
Lines: 60
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bfd0541$1@forums-1-dub>
Date: 26 May 2010 04:25:53 -0700
X-Trace: forums-1-dub 1274873153 10.22.241.152 (26 May 2010 04:25:53 -0700)
X-Original-Trace: 26 May 2010 04:25:53 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29256
Article PK: 78490


<Lisa Chow> wrote in message news:4bfcd1a4.56c5.1681692777@sybase.com...
> Hi Carl,
>
> Thank you for the SQL statement you provided in your
> posting. I will use it instead of the one I created.
>
> Just curious thought... would the below one work if it was
> guarentteed that there were only one user set of tables?
>
> select name, rowcnt(sysindexes.doampg) from sysindexes where
> name in (select name from sysobjects where type = 'U')
>
> Thank you,
>
> Lisa
>

Yes, it would work. But you would be promoting a bad precedent with regards
to coding practices. (Sometime in the future same-named tables are created
by a different user and, possibly, a lot of your code returns wrong
results.)

>
>
>> <Lisa Chow> wrote in message
>> > news:4bfb8979.7c8b.1681692777@sybase.com... Hi,
>> >
>> > I'm using the below SQL statements...
>> >
>> >
>> > ------------------------
>> > For ASE 12.5.4
>> > ------------------------
>> > select name, rowcnt(sysindexes.doampg) from sysindexes
>> > where name in (select name from sysobjects where type =
>> > 'U') order by name
>> >
>> > go
>> >
>>
>> select 'Table' = o.name, 'Rows' = rowcnt
>> (i.sysindxes.doampg) from sysinobjects o,
>> sysindexes i
>> where o.type = 'U'
>> and i.id = o.id
>> and i.indid < 2
>>
>> The PK for sysindexes is (id, indid) and the "base table
>> information" is in indid 0 or 1. Only one of these will
>> exist for a table. If the table has an APL clustered
>> index then the "base info" will be stored in indid 1. If
>> the table does not have an APL clustered index then the
>> "base info" will be with indid 0. Note that you do not
>> want to use "name" for the join since there could be
>> same-named tables with different owners.
>>
>>


"Mark A. Parsons" <iron_horse Posted on 2010-05-23 12:15:37.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
References: <4bf90b56.3b69.1681692777@sybase.com>
In-Reply-To: <4bf90b56.3b69.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100512-1, 05/12/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bf91c69$1@forums-1-dub>
Date: 23 May 2010 05:15:37 -0700
X-Trace: forums-1-dub 1274616937 10.22.241.152 (23 May 2010 05:15:37 -0700)
X-Original-Trace: 23 May 2010 05:15:37 -0700, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29242
Article PK: 78476

Have you tried running your code, yet? Do you get errors, and if so, what are the errors?

Which version of ASE are you using?

If you require 100% accuracy on the row counts then the cursor method should suffice, but I'd wrap the 'select' inside
an execute immediate construct, ie, exec('select ....'). [NOTE: In an active database the row counts from some of your
tables may be constantly changing; point being that your query's results may only be 100% accurate for a brief period of
time.]

If rows counts can be close/approximate, I'd suggest running 'sp_flushstats' followed by a set-based query that either
a) uses the rowcount()/rowcnt() function or b) pulls the row count values from systabstats.rowcnt column. [This query
should be rather quick because the dataserver does not actually go out and count each record in each table, but rather
provides current statistics on what the dataserver *thinks* the current row counts are for each table.]

Lisa Chow wrote:
> Please help me troubleshoot the below code. I'm trying to
> convert it over from MS SQL Server to Sybase. The MS SQL
> Server code is very similar to Sybase ASE, but different in
> places. I'm hoping that the below code is correct and will
> work on ASE, but was hoping to get your feedback. Is there
> a better way to do this?
>
> ------------------------------------------------
>
> declare table_cursor cursor
> for select name from sysobjects where type='U'
> for read only
>
> declare @table_name sysname
>
> set @total_recs = 0
>
> open table_cursor
> fetch table_cursor into @table_name
> while @@sqlstatus = 0
> begin
>
> print @tablename + ' - '
> select count(*) from @table_name
>
> fetch table_cursor into @table_name
> end
> close table_cursor
> deallocate cursor table_cursor
>
> ------------------------------------------------
>
> Thank you
>
> Lisa


Lisa Chow Posted on 2010-05-26 00:14:28.0Z
Sender: 7c70.4bfb8858.1804289383@sybase.com
From: Lisa Chow
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bfc67e4.488e.1681692777@sybase.com>
References: <4bf91c69$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 May 2010 17:14:28 -0700
X-Trace: forums-1-dub 1274832868 10.22.241.41 (25 May 2010 17:14:28 -0700)
X-Original-Trace: 25 May 2010 17:14:28 -0700, 10.22.241.41
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29252
Article PK: 78486

Hi Mark,

Thank you for your response.

Sorry for my late response. We are using ASE 12.5.4 and ASE
15.0.3.

I'm following your advice and using the rowcnt() and
row_count functions. I tried to run the code creating the
cursor, but wasn't successful in getting it to work. I
added the exec('select...') as you advised. I'm sure that
I'm making a simple typo or something of the like that may
be causing it to not work. The @@sqlstatus comes back with
the value of 2. Not sure why.

Thank you

> Have you tried running your code, yet? Do you get errors,
> and if so, what are the errors?
>
> Which version of ASE are you using?
>
> If you require 100% accuracy on the row counts then the
> cursor method should suffice, but I'd wrap the 'select'
> inside an execute immediate construct, ie, exec('select
> ....'). [NOTE: In an active database the row counts from
> some of your tables may be constantly changing; point
> being that your query's results may only be 100% accurate
> for a brief period of time.]
>
> If rows counts can be close/approximate, I'd suggest
> running 'sp_flushstats' followed by a set-based query that
> either a) uses the rowcount()/rowcnt() function or b)
> pulls the row count values from systabstats.rowcnt column.
> [This query should be rather quick because the
> dataserver does not actually go out and count each record
> in each table, but rather provides current statistics on
> what the dataserver *thinks* the current row counts are
> for each table.]
>
>
>
> Lisa Chow wrote:
> > Please help me troubleshoot the below code. I'm trying
> > to convert it over from MS SQL Server to Sybase. The MS
> > SQL Server code is very similar to Sybase ASE, but
> > different in places. I'm hoping that the below code is
> > correct and will work on ASE, but was hoping to get your
> > feedback. Is there a better way to do this?
> >
> > ------------------------------------------------
> >
> > declare table_cursor cursor
> > for select name from sysobjects where type='U'
> > for read only
> >
> > declare @table_name sysname
> >
> > set @total_recs = 0
> >
> > open table_cursor
> > fetch table_cursor into @table_name
> > while @@sqlstatus = 0
> > begin
> >
> > print @tablename + ' - '
> > select count(*) from @table_name
> >
> > fetch table_cursor into @table_name
> > end
> > close table_cursor
> > deallocate cursor table_cursor
> >
> > ------------------------------------------------
> >
> > Thank you
> >
> > Lisa


Lisa Chow Posted on 2010-05-26 11:16:50.0Z
Sender: 48d3.4bfc6917.1804289383@sybase.com
From: Lisa Chow
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bfd0322.5e00.1681692777@sybase.com>
References: <4bf91c69$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 May 2010 04:16:50 -0700
X-Trace: forums-1-dub 1274872610 10.22.241.41 (26 May 2010 04:16:50 -0700)
X-Original-Trace: 26 May 2010 04:16:50 -0700, 10.22.241.41
Lines: 105
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29254
Article PK: 78487

Hi Mark,

I'm following your advice and wrapped the sql statements
with exec('select... '), but seems to be still failing. I'm
running it in both ASE 12.5.4 and 15.0.3. The error is
saying that the 'declare cursor' has to run in it's own
batch.

I'm running it from a flat file called row_count_sql.sql.
Then launching isql with the -i flag to point to the flat
file.

------------------------------------------------

declare table_cursor cursor
for select name from sysobjects where type='U'
for read only

declare @table_name sysname

open table_cursor
fetch table_cursor into @table_name
while @@sqlstatus = 0
begin

exec('select' + @table_name + ' - ')
exec('select count(*) from' + @table_name)

fetch table_cursor into @table_name
end
close table_cursor
deallocate cursor table_cursor

------------------------------------------------

Am I overlooking something simple? Will the above code run
in both ASE 12.5.4 and ASE 15.0.3?

Thank you

> Have you tried running your code, yet? Do you get errors,
> and if so, what are the errors?
>
> Which version of ASE are you using?
>
> If you require 100% accuracy on the row counts then the
> cursor method should suffice, but I'd wrap the 'select'
> inside an execute immediate construct, ie, exec('select
> ....'). [NOTE: In an active database the row counts from
> some of your tables may be constantly changing; point
> being that your query's results may only be 100% accurate
> for a brief period of time.]
>
> If rows counts can be close/approximate, I'd suggest
> running 'sp_flushstats' followed by a set-based query that
> either a) uses the rowcount()/rowcnt() function or b)
> pulls the row count values from systabstats.rowcnt column.
> [This query should be rather quick because the
> dataserver does not actually go out and count each record
> in each table, but rather provides current statistics on
> what the dataserver *thinks* the current row counts are
> for each table.]
>
>
>
> Lisa Chow wrote:
> > Please help me troubleshoot the below code. I'm trying
> > to convert it over from MS SQL Server to Sybase. The MS
> > SQL Server code is very similar to Sybase ASE, but
> > different in places. I'm hoping that the below code is
> > correct and will work on ASE, but was hoping to get your
> > feedback. Is there a better way to do this?
> >
> > ------------------------------------------------
> >
> > declare table_cursor cursor
> > for select name from sysobjects where type='U'
> > for read only
> >
> > declare @table_name sysname
> >
> > set @total_recs = 0
> >
> > open table_cursor
> > fetch table_cursor into @table_name
> > while @@sqlstatus = 0
> > begin
> >
> > print @tablename + ' - '
> > select count(*) from @table_name
> >
> > fetch table_cursor into @table_name
> > end
> > close table_cursor
> > deallocate cursor table_cursor
> >
> > ------------------------------------------------
> >
> > Thank you
> >
> > Lisa


Carl Kayser Posted on 2010-05-26 11:22:57.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4bf91c69$1@forums-1-dub> <4bfd0322.5e00.1681692777@sybase.com>
Subject: Re: using cursor to get a row count for all user tables...
Lines: 114
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bfd0491$1@forums-1-dub>
Date: 26 May 2010 04:22:57 -0700
X-Trace: forums-1-dub 1274872977 10.22.241.152 (26 May 2010 04:22:57 -0700)
X-Original-Trace: 26 May 2010 04:22:57 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29255
Article PK: 78488


<Lisa Chow> wrote in message news:4bfd0322.5e00.1681692777@sybase.com...
> Hi Mark,
>
> I'm following your advice and wrapped the sql statements
> with exec('select... '), but seems to be still failing. I'm
> running it in both ASE 12.5.4 and 15.0.3. The error is
> saying that the 'declare cursor' has to run in it's own
> batch.
>
> I'm running it from a flat file called row_count_sql.sql.
> Then launching isql with the -i flag to point to the flat
> file.
>
> ------------------------------------------------
>
> declare table_cursor cursor
> for select name from sysobjects where type='U'
> for read only
>
> declare @table_name sysname
>
> open table_cursor
> fetch table_cursor into @table_name
> while @@sqlstatus = 0
> begin
>
> exec('select' + @table_name + ' - ')
> exec('select count(*) from' + @table_name)
>
> fetch table_cursor into @table_name
> end
> close table_cursor
> deallocate cursor table_cursor
>
> ------------------------------------------------
>
> Am I overlooking something simple? Will the above code run
> in both ASE 12.5.4 and ASE 15.0.3?
>
> Thank you
>
>
>
>
>
>> Have you tried running your code, yet? Do you get errors,
>> and if so, what are the errors?
>>
>> Which version of ASE are you using?
>>
>> If you require 100% accuracy on the row counts then the
>> cursor method should suffice, but I'd wrap the 'select'
>> inside an execute immediate construct, ie, exec('select
>> ....'). [NOTE: In an active database the row counts from
>> some of your tables may be constantly changing; point
>> being that your query's results may only be 100% accurate
>> for a brief period of time.]
>>
>> If rows counts can be close/approximate, I'd suggest
>> running 'sp_flushstats' followed by a set-based query that
>> either a) uses the rowcount()/rowcnt() function or b)
>> pulls the row count values from systabstats.rowcnt column.
>> [This query should be rather quick because the
>> dataserver does not actually go out and count each record
>> in each table, but rather provides current statistics on
>> what the dataserver *thinks* the current row counts are
>> for each table.]
>>
>>
>>
>> Lisa Chow wrote:
>> > Please help me troubleshoot the below code. I'm trying
>> > to convert it over from MS SQL Server to Sybase. The MS
>> > SQL Server code is very similar to Sybase ASE, but
>> > different in places. I'm hoping that the below code is
>> > correct and will work on ASE, but was hoping to get your
>> > feedback. Is there a better way to do this?
>> >
>> > ------------------------------------------------
>> >
>> > declare table_cursor cursor
>> > for select name from sysobjects where type='U'
>> > for read only
>> >
>> > declare @table_name sysname
>> >
>> > set @total_recs = 0
>> >
>> > open table_cursor
>> > fetch table_cursor into @table_name
>> > while @@sqlstatus = 0
>> > begin
>> >
>> > print @tablename + ' - '
>> > select count(*) from @table_name
>> >
>> > fetch table_cursor into @table_name
>> > end
>> > close table_cursor
>> > deallocate cursor table_cursor
>> >
>> > ------------------------------------------------
>> >
>> > Thank you
>> >
>> > Lisa

Where are your "go" statements? I believe that there should be only one -
at the end. If there is one earlier, say, after the cursor declaration then
the context is lost.


"Mark A. Parsons" <iron_horse Posted on 2010-05-26 17:26:43.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
References: <4bf91c69$1@forums-1-dub> <4bfd0322.5e00.1681692777@sybase.com> <4bfd0491$1@forums-1-dub>
In-Reply-To: <4bfd0491$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100526-1, 05/26/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bfd59d3$1@forums-1-dub>
Date: 26 May 2010 10:26:43 -0700
X-Trace: forums-1-dub 1274894803 10.22.241.152 (26 May 2010 10:26:43 -0700)
X-Original-Trace: 26 May 2010 10:26:43 -0700, vip152.sybase.com
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29258
Article PK: 78491


Carl Kayser wrote:
> Where are your "go" statements? I believe that there should be only one -
> at the end. If there is one earlier, say, after the cursor declaration then
> the context is lost.

For stored procs ... yes, only one 'go' is required at the end of the stored proc definition.

For SQL batches ... the 'declare cursor' command has to reside within its own query batch, so an additional 'go' is
required after the 'declare cursor' command.

--------

I have a feeling you already know this Carl, so what's the problem ... posted before you had your first cup of coffee? ;-)


Carl Kayser Posted on 2010-05-26 18:09:53.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4bf91c69$1@forums-1-dub> <4bfd0322.5e00.1681692777@sybase.com> <4bfd0491$1@forums-1-dub> <4bfd59d3$1@forums-1-dub>
Subject: Re: using cursor to get a row count for all user tables...
Lines: 26
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bfd63f1$1@forums-1-dub>
Date: 26 May 2010 11:09:53 -0700
X-Trace: forums-1-dub 1274897393 10.22.241.152 (26 May 2010 11:09:53 -0700)
X-Original-Trace: 26 May 2010 11:09:53 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29259
Article PK: 78492


"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4bfd59d3$1@forums-1-dub...
>
>
> Carl Kayser wrote:
>> Where are your "go" statements? I believe that there should be only
>> one - at the end. If there is one earlier, say, after the cursor
>> declaration then the context is lost.
>
> For stored procs ... yes, only one 'go' is required at the end of the
> stored proc definition.
>
> For SQL batches ... the 'declare cursor' command has to reside within its
> own query batch, so an additional 'go' is required after the 'declare
> cursor' command.
>
> --------
>
> I have a feeling you already know this Carl, so what's the problem ...
> posted before you had your first cup of coffee? ;-)

[GROAN] I'm getting too old for this. Wait ... maybe there's hope yet.
Perhaps my first can of soda in the AM was caffeine-free!


"Mark A. Parsons" <iron_horse Posted on 2010-05-26 17:21:18.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
References: <4bf91c69$1@forums-1-dub> <4bfd0322.5e00.1681692777@sybase.com>
In-Reply-To: <4bfd0322.5e00.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100526-1, 05/26/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bfd588e$1@forums-1-dub>
Date: 26 May 2010 10:21:18 -0700
X-Trace: forums-1-dub 1274894478 10.22.241.152 (26 May 2010 10:21:18 -0700)
X-Original-Trace: 26 May 2010 10:21:18 -0700, vip152.sybase.com
Lines: 119
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29257
Article PK: 78494

You've stated that your code is failing, but you haven't provided us with a description of the failure. Is the code
generating errors, and if so, what are the errors?

FWIW, the first select (select @table_name '-') doesn't have to be issued within an exec()).

I recommend you perform the following:

isql -U... -S... -P... -i row_count_sql.sql -e -o row_count_sql.txt

Then reply to this post and attach the output file (row_count_sql.txt) to your post. Do not edit/remove anything from
the output file (row_count_sql.txt).

Lisa Chow wrote:
> Hi Mark,
>
> I'm following your advice and wrapped the sql statements
> with exec('select... '), but seems to be still failing. I'm
> running it in both ASE 12.5.4 and 15.0.3. The error is
> saying that the 'declare cursor' has to run in it's own
> batch.
>
> I'm running it from a flat file called row_count_sql.sql.
> Then launching isql with the -i flag to point to the flat
> file.
>
> ------------------------------------------------
>
> declare table_cursor cursor
> for select name from sysobjects where type='U'
> for read only
>
> declare @table_name sysname
>
> open table_cursor
> fetch table_cursor into @table_name
> while @@sqlstatus = 0
> begin
>
> exec('select' + @table_name + ' - ')
> exec('select count(*) from' + @table_name)
>
> fetch table_cursor into @table_name
> end
> close table_cursor
> deallocate cursor table_cursor
>
> ------------------------------------------------
>
> Am I overlooking something simple? Will the above code run
> in both ASE 12.5.4 and ASE 15.0.3?
>
> Thank you
>
>
>
>
>
>> Have you tried running your code, yet? Do you get errors,
>> and if so, what are the errors?
>>
>> Which version of ASE are you using?
>>
>> If you require 100% accuracy on the row counts then the
>> cursor method should suffice, but I'd wrap the 'select'
>> inside an execute immediate construct, ie, exec('select
>> ....'). [NOTE: In an active database the row counts from
>> some of your tables may be constantly changing; point
>> being that your query's results may only be 100% accurate
>> for a brief period of time.]
>>
>> If rows counts can be close/approximate, I'd suggest
>> running 'sp_flushstats' followed by a set-based query that
>> either a) uses the rowcount()/rowcnt() function or b)
>> pulls the row count values from systabstats.rowcnt column.
>> [This query should be rather quick because the
>> dataserver does not actually go out and count each record
>> in each table, but rather provides current statistics on
>> what the dataserver *thinks* the current row counts are
>> for each table.]
>>
>>
>>
>> Lisa Chow wrote:
>>> Please help me troubleshoot the below code. I'm trying
>>> to convert it over from MS SQL Server to Sybase. The MS
>>> SQL Server code is very similar to Sybase ASE, but
>>> different in places. I'm hoping that the below code is
>>> correct and will work on ASE, but was hoping to get your
>>> feedback. Is there a better way to do this?
>>>
>>> ------------------------------------------------
>>>
>>> declare table_cursor cursor
>>> for select name from sysobjects where type='U'
>>> for read only
>>>
>>> declare @table_name sysname
>>>
>>> set @total_recs = 0
>>>
>>> open table_cursor
>>> fetch table_cursor into @table_name
>>> while @@sqlstatus = 0
>>> begin
>>>
>>> print @tablename + ' - '
>>> select count(*) from @table_name
>>>
>>> fetch table_cursor into @table_name
>>> end
>>> close table_cursor
>>> deallocate cursor table_cursor
>>>
>>> ------------------------------------------------
>>>
>>> Thank you
>>>
>>> Lisa


Lisa Posted on 2010-05-23 11:07:22.0Z
Sender: 3221.4bf8e7ff.1804289383@sybase.com
From: Lisa
Newsgroups: sybase.public.ase.general
Subject: Re: using cursor to get a row count for all user tables...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bf90c6a.3bb6.1681692777@sybase.com>
References: <4bf90b56.3b69.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 May 2010 04:07:22 -0700
X-Trace: forums-1-dub 1274612842 10.22.241.41 (23 May 2010 04:07:22 -0700)
X-Original-Trace: 23 May 2010 04:07:22 -0700, 10.22.241.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29240
Article PK: 78477

Sorry, I found type-os in my posting... I corrected them
below...

------------------------------------------------

declare table_cursor cursor
for select name from sysobjects where type='U'
for read only

declare @table_name sysname

open table_cursor
fetch table_cursor into @table_name
while @@sqlstatus = 0
begin

print @tablename + ' - '
select count(*) from @table_name

fetch table_cursor into @table_name
end
close table_cursor
deallocate cursor table_cursor

------------------------------------------------

> Please help me troubleshoot the below code. I'm trying to
> convert it over from MS SQL Server to Sybase. The MS SQL
> Server code is very similar to Sybase ASE, but different
> in places. I'm hoping that the below code is correct and
> will work on ASE, but was hoping to get your feedback. Is
> there a better way to do this?
>
> ------------------------------------------------
>
> declare table_cursor cursor
> for select name from sysobjects where type='U'
> for read only
>
> declare @table_name sysname
>
> set @total_recs = 0
>
> open table_cursor
> fetch table_cursor into @table_name
> while @@sqlstatus = 0
> begin
>
> print @tablename + ' - '
> select count(*) from @table_name
>
> fetch table_cursor into @table_name
> end
> close table_cursor
> deallocate cursor table_cursor
>
> ------------------------------------------------
>
> Thank you
>
> Lisa