on 11-25-2013 4:19 PM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.