January 21, 2008 at 9:06 am
I'm doing some performance investiagations and came across what appeared to be a useful query, but unfortunately it uses the OBJECT_NAME function with 2 arguments, ObjectID and DatabaseID. This form was introduced with SQL 2005 SP2, which isn't running on our production servers yet.
Without the database id, OBJECT_NAME will only resolve names for objects in the currently selected database (usually returning NULL for unknown object, but BOL says it may return incorrect results).
Does anyone know a way to simulate OBJECT_NAME(object_id, db_id), that is to get the name of an object in any database given the object id and database id, prior to SP2, specifically on SP1?
Thanks.
Derek
January 21, 2008 at 9:13 am
create a function, query sys.objects using your criteria.
January 21, 2008 at 10:35 am
Hi Steve,
Unfortunately that doesn't work.
On my test server,
"use scratch;select count(*) from sys.objects" returns 61
"use perf;select count(*) from sys.objects" returns 1609
I need a pointer to whatever sys.objects is looking at so I can write the function which does
"select ... from sys.??? join sys.??? ... where objid=@objectid and dbid=@dbid".
I already tried sys.objects, sys.sysobjects and sys.all_objects (that returned counts of 1817 & 3365). None of them seem to have a database id field. They all seem to act as views on the currently selected database.
Derek.
Derek
January 21, 2008 at 11:54 am
here's an SP. problem making a function is the need for dynamic sql, but maybe this wlll get u going in the right direction
Create Proc usp_objName
@object_id int,
@db_id smallint
AS
Declare @db varchar(128)
Select @db = db_name(@db_id)
Exec ('SELECT name FROM ' + @db + '..sysobjects WHERE id = ' + @object_id)
January 22, 2008 at 3:25 am
russell (1/21/2008)
here's an SP. problem making a function is the need for dynamic sql, but maybe this wlll get u going in the right direction
Thanks for the suggestion, but I'd already tried a variation of this.
As you say, the problem is that functions don't allow dynamic SQL so I was hoping that someone would be able to point me to a system table or function I wasn't previously aware of that would do the mapping. But perhaps that's why they had to add the extra parameter for SP2! 🙂
It looks like I'm going to have to rewrite the whole query as dynamic SQL. 🙁
Derek
January 22, 2008 at 4:17 am
I'd still be interested if there's an easier way, but my current solution is...
create table ##db_obj (
dbid int,
objectid int,
objname varchar(128)
)
/*
Temp table #T contains columns dbid(int) and objectid(int) with the values needed to be looked up
*/
declare @s-2 varchar(max)
set @s-2 = ''
select @s-2=@s+'insert ##db_obj (dbid,objectid,objname) select '+
convert(varchar(10),dbid)+',object_id,name from '+
db_name(dbid)+'.sys.objects where object_id in(select objectid from #T where dbid='+convert(varchar(10),dbid)+');'
from #T group by dbid
exec (@s)
select * from ##db_obj a
join #T b on a.dbid=b.dbid and a.objectid=b.objectid
Derek
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply