cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor
0 Kudos

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

Former Member
0 Kudos

wonderful Bret, what about the name of the owner?  I'm assuming object_owner_id is querying the appropriate sysobjects?

Former Member
0 Kudos

did not find elegant approach, just some workarounds, which mostly already were mentioned in initial message

1) select user_name(object_owner_id(1)) refers only to the users in current database

so alternatively would be nice to have second argument for user_name

2) it is possible to create function like this

create function owner_name(@objid int, @dbid int = null)

returns longsysname

as

begin

    declare @n longsysname

    declare @sql varchar(8000)

    set @objid = isnull(@objid,db_id())

    set @sql = 'select @n=name from ' + db_name(@dbid) + '..sysusers where uid=' + convert(varchar,object_owner_id(@objid, @dbid))

    execute (@sql)

    return @n

end

but this is actually what you said "create a dynamic query for each and every row"

3) if I correctly understand your idea about look up table, you meant to create look up table for object-to-user,.. suppose object_owner_id might bring some new life to this idea, just create look up table/view for sysusers

create view allsysusers

as

select db_id('pubs2') dbid , uid, name from pubs2..sysusers

union all

select db_id('pubs3') dbid , uid, name from pubs3..sysusers

select u.name, o.ObjectName

from monOpenObjectActivity o

join allsysusers u on object_owner_id(o.ObjectID, o.DBID) = u.uid and o.DBID = u.dbid

Answers (0)