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?

[Mark_A_Parsons]

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
• 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] 

[fjdelalamo]

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.

[fjdelalamo]

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

[fjdelalamo]

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.

[Mark_A_Parsons]

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.