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

[fjdelalamo]

Hi, Mark: first of all, thank you for replying and for wanting to help me.

I don’t know how to thank you for each comment individually, so I’m replying with this message. Let me explain: in version 12.5.x, I’ve tried what you suggested and have opted for a compromise: creating a stored procedure in both databases as follows:

CREATE PROC test
@table VARCHAR (255)
AS
SELECT ROWCNT(doampg)
FROM sysindexes
WHERE id = OBJECT_ID(@table)
AND indid = 0

I then simply run `database..test <table name>` and get the result. I don’t like creating objects for this purpose, but given that I cannot run a cross-database query for this, it’s a good workaround.

Regards

[Mark_A_Parsons]

Ah, yes, this solution (local proc in each database) works, too! 🙂

Whenever I see a requirement to run a proc in multiple databases I automatically think global stored proc (a habit formed over a couple decades) so I sometimes forget the KISS principal (eg, separate proc in each database). Having said that ...

I don't currently have access to an ASE 12.5.x instance so I'm curious to know: Did you try creating a global stored proc in ASE 12.5.x and if so, did you have any issues?