Home » Databases » Sybase » ASE » SAP Sybase ASE MDA Tables: Why is the OwnerName and OwnerID omitted in most of the MDA tables?

SAP Sybase ASE MDA Tables: Why is the OwnerName and OwnerID omitted in most of the MDA tables?

If you look SAP Sybaseat the MDA poster, you will find the object names, the database names and the like but not the owner_id or owner_name. Let’s pick on monOpenObjectActivity, one of the most heavily used mda tables

Having monOpenObjectActivity is great but what if I have two tables named the same in the same database with a different owner? Such a scenario is not uncommon in a development system when having a separate database for each developer is not practical.

A row in monOpenObjectActivity could be mydb.john.table_a or mydb.jane.table_a. While I do have the object id, there is no owner_name(DBID, OBJID) function, so I need to go into each individual database and query sysobjects. What a hassle! ASE already knows, or should know, which owner id the object belongs to so why need I have to create a dynamic query for each and every row in monOpenObjectActivity? Sure, I could create a look up table but it wouldn’t be able to handle temporary objects (e.g. #table) very well.

The following tables have Owner something in the column names:

1> select convert(varchar(30), object_name(id)) as "Object Name", convert(varchar(30), name) as "Column Name" from syscolumns where lower(name) like "%owner%"
2> go
Object Name                    Column Name
------------------------------ ------------------------------
monProcessObject               OwnerUserID
monCachedProcedures            OwnerUID
monCachedProcedures            OwnerName
monProcessProcedures           OwnerUID
monSpinlockActivity            OwnerPID
monSpinlockActivity            LastOwnerPID
monMemoryUsage                 PoolOwnerKPID
monProcessProcedures           OwnerName
monCachedObject                OwnerUserID
monCachedObject                OwnerName
sp_namecrack                   @owner

I think we need two functions: owner_name(DBID, OBJID) and owner_id(DBID, OBJID)

We also need the OwnerID and preferrably OwnerName added to the appropriate MDA tables.

What do you think?

Share Button

Comments

  1. Bret Halford Nov 25, 2013 5:41 PM (in response to Jason Froebe)
    Hi Jason,

    We did sneak in an object_owner_id() function in 15.5.

    http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36271.1572/html/blocks/blocks179.htm

    -bret

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect