rowcnt cross-database

[Mark_A_Parsons]

Good morning, 

Suppose I have two databases, A and B, hosted on the same ASE server. While connected to database A, I need to retrieve the value of rowcnt(doampg) for a table in database B.

For example, I have tried the following query:

SELECT rowcnt(doampg)
FROM B..sysindexes
WHERE id = object_id("B..table_name")
  AND indid = 0

However, the result is always 0 when I execute this query from database A. If I switch my current database context to B and execute the equivalent query there, I get the expected information.

Is there a way to retrieve the correct value for a table in database B while remaining in database A, without having to switch the current database context? More specifically, is there an alternative approach that does not require querying systabstats after executing flushstats?

I'm having this problem in ASE 12.5.x, but in later versions, "rowcnt" no longer exists and "row_count" is used instead; perhaps my problem was one of the reasons for the change.

Thank you very much for your time and help. This is an important issue for me, so any technical explanation, documentation references, or suggestions for further testing would be extremely valuable.


Regards and thanks in advanced

[Mark_A_Parsons]

The rowcnt() function was deprecated with the introduction of ASE 15.0.

Could you update the question with the complete output from the query: select @@version

[Sandra_Rossi]

NB: I was curious about rowcnt, here are the URLs to the documentation; rowcnt in ASE 12.5.1 -> row_count in ASE 15.5 as shown in Reference Manual: Building Blocks > Transact-SQL Functions.

12.5.1: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36271_1251/html/blocks/X91424.htm

15.0 (archive in PDF format): Release Bulletin Adaptive Server® Enterprise Version 15.0 > 7. Changes that may affect existing appl...: "Built-in functions that provide space information like data_pgs, reserved_pgs, used_pgs, ptn_data_pgs and rowcnt have been deprecated. They have been replaced with data_pages, reserved_pages, used_pages, and row_count."

15.5: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36271_1251/html/blocks/X91424.htm

16.0: https://help.sap.com/docs/SAP_ASE/2df920cd5b1644e5a065d55b637d5e92/aae06819bc2b1014af108fd1a517367e....

Sandra

[Mark_A_Parsons]

Finally found an ASE 12.5.4 instance (supports the rowcnt() function) so I can demonstrate a global stored procedure running OP's rowcnt() query ...

Setup:

-- create table in both databases (db1, db2)

create table tab1 (a int, b varchar(100))
go

-- leave the table empty in db1; populate the table in db2

insert tab1 select id,'stuff' from sybsystemprocs..sysobjects
go

-- run a modified copy of OP's query in both databases;
-- results are reformatted for readability

select  db_name() as 'db', object_name(id) as 'table', rowcnt(doampg) as 'rowcnt'
from    sysindexes
where   id = object_id('tab1')
and     indid = 0
go

 db  table rowcnt
 --- ----- ------
 db1 tab1       0
 db2 tab1     477

Wrapping the modified query in a stored procedure wrapper and installing in the sybsystemprocs database:

use sybsystemprocs
go

create procedure sp__rowcnt
@objname varchar(255) ='sysobject'
as

select  db_name() as 'db', object_name(id) as 'table', rowcnt(doampg) as 'rowcnt'
from    sysindexes
where   id = object_id(@objname)
and     indid = 0
go

From the master database we'll run sp__rowcnt against the two tables:

use master
go

exec db1..sp__rowcnt 'tab1'
exec db2..sp__rowcnt 'dbo.tab1'
go

 db  table rowcnt
 --- ----- ------
 db1 tab1       0

 db  table rowcnt
 --- ----- ------
 db2 tab1     477