MDA Tables: Why is the owner_name omitted in most of the MDA tables?

89 Views

Hi all,

If you look at 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?