How to determine the SQL Server object name from object id and database id?

Question

I need the behaviour of SQL Server 2005 where function OBJECT_NAME takes two arguments, obj id and db id, while SQL Server 2000 takes only obj id so the execution must be in the context of the database to which inspected object belongs to.

Solution must be possible to implement in a function, so it can be used in a select query.

Solution 1

In SQL 2005 and up it is of course trivial to do this. The problem is SQL 2000. I used 2000 a lot back when, but no longer have access to any installations of it; the rest of this is largely from memory, and may be inaccurate.

The key thing is how to retrieve data from a database other than the "current" database, when you cannot know what that other database (or databases) will be at the time the code is written. (Yes, the db_id parameter is very convenient!) For this problem and for similar problems, the general work-around is to create dynamic code, something like:

SET @Command = 'select name from ' + @dbname + '.dbo.sysobjects where object_id = ' + @ObjectId
EXECUTE (@Command)

The problem is, I'm pretty sure you can't run dynamic code within functions (or perhaps just within SQL 2000 functions).

You might have to resort to creating a temp table, populating it via dynamic query, and then using it within the "main" query you are trying to write. Psuedo code would be like:

CREATE #TempTable
IF SQL2000 or earlier
    INSERT #TempTable EXECUTE (select data from TargetDb.dbo.sysobjects)
    --  Note that the entire insert may need to be in the dynamic statement
ELSE
    INSERT #TempTable SELECT [from query based on object_id]

SELECT [the data you need]
 from YourTable
  join #TempTable

Solution 2

In SQL 2008 and up, use:

OBJECT_NAME ( object_id [, database_id ] )  

for example:

SELECT TOP 10
    object_schema_name(objectid, dbid) as [SchemaName], 
    object_name(objectid, dbid) as [ObjectName],
    e.*
    from sys.dm_exec_cached_plans P
    CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E

Comments

[Martin Smith]

Yep. You can't use dynamic SQL. You get the error "Only functions and extended stored procedures can be executed from within a function."

[Philip Kelley]

I have to foolishly mention I once read an article wherein some great brain embedded a call to xp_cmdshell within a function. Imagine shelling out, querying a website, and returning data to your query, once per row. Please, please don't do that!

[Tomek]

Philip, you described the reasons why I asked that questions. I was prepared that there might not be a neat solution to that. I needed this to use with sp_lock result set, so the approach with creating temp table upfront (cursor through all DBs in the system and pooling all from sysobjects) might be a bit expensive especially when called often, but definitely some way to go. Thanks for the answer.