Question Re: rowcnt cross-database in Technology Q&A

rowcnt cross-database

fjdelalamo — Thu, 22 Oct 2026 07:34:49 GMT

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

Re: rowcnt cross-database

Mark_A_Parsons — Wed, 21 Oct 2026 19:59:23 GMT

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

Re: rowcnt cross-database

Mark_A_Parsons — Tue, 27 Oct 2026 16:59:19 GMT

Since the rowcnt() function was deprecated with the introduction of ASE 15.0 I'm going to assume OP is running ASE 12.x in which case I'd suggest a global stored procedure (defined in the sybsystemprocs database with a name that starts with 'sp_' - though I'd suggest 'sp__' to distinguish from system stored procedures).

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

For demo purposes I've created MyObject (table in db1; procedure in db2) :

use db1 go create table MyObject (a int null ,b int null ) go use db2 go create procedure MyObject as print 'hi' go

Running the following query in both databases to obtain details on MyObject :

select db_name() as 'database',name as 'object name',id,type from sysobjects where id = object_id('MyObject') go -- results reformatted for readability database object name id type -------- ----------- ---------- ---- db1 MyObject 1657105963 U db2 MyObject 1408005016 P

We'll incorporate this same code in a global procedure (sp__help) we'll place in the sybsystemprocs database :

use sybsystemprocs go create procedure sp__help @objname varchar(255) ='sysobject' as select db_name() as 'database',name as 'object name',id,type from sysobjects where id = object_id(@objname) go

For demo purposes we'll make our calls to sp__help from the master database :

use master go exec db1..sp__help 'MyObject' exec db2..sp__help 'dbo.MyObject' go database object name id type -------- ---------------- ---------- ---- db1 MyObject 1657105963 U database object name id type -------- ---------------- ---------- ---- db2 MyObject 1408005016 P

Notice that we use the database name (db1, db2) to fully qualify the stored proc name (as opposed to fully qualifying the object name as in OP's sample query).

NOTES:

while the above code was tested on an ASE 16.0 SP04 PL04 instance, I would expect it to also work on ASE 12.x (see separate answer demonstrating a global stored procedure running OP's rowcnt() query against an ASE 12.5.4 instance)
if there's a requirement to capture the global procedure's output for further T-SQL processing (eg, capture the row count result from OP's query), the global procedure can be modified to include output parameters [I'll leave this for OP to test]

Re: rowcnt cross-database

Sandra_Rossi — Thu, 22 Oct 2026 04:15:31 GMT

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 applications > 7.3 Changes that affect database administration operations > 7.3.1 Usage of system built-ins: "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.html?locale=en-US

Sandra

Re: rowcnt cross-database

fjdelalamo — Thu, 22 Oct 2026 09:02:43 GMT

Hi Mark, first of all, thank you for replying and for wanting to help. Let me explain: in 12.5.x, I’ve tried what you suggested and have opted for a compromise solution – 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 Then I simply run `database..test <table name>` and I get the result. I don’t like creating objects for this purpose, but it’s a good workaround.

Re: rowcnt cross-database

fjdelalamo — Thu, 22 Oct 2026 09:06:45 GMT

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

Re: rowcnt cross-database

fjdelalamo — Thu, 22 Oct 2026 09:13:08 GMT

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

Re: rowcnt cross-database

Mark_A_Parsons — Thu, 22 Oct 2026 17:13:11 GMT

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?

Re: rowcnt cross-database

fjdelalamo — Thu, 22 Oct 2026 17:30:37 GMT

Hi Mark, thanks for your interest. It didn’t work for me, but reading your comments gave me the idea about the stored procedure. By the way, I can’t post a comment below your latest one – I don’t know how to do it, so I have to do it here. Do you know how to post a comment below your latest one? I can’t see the ‘Comment’ option.

Re: rowcnt cross-database

Mark_A_Parsons — Fri, 23 Oct 2026 13:52:50 GMT

Instead of commenting on comments, you comment on the associated answer. Unlike other website discussion/forum systems that can support nested comments, this (SAP) system has the single level of comments associated with an answer.

Re: rowcnt cross-database

Mark_A_Parsons — Tue, 27 Oct 2026 16:55:36 GMT

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