How to determine sql server object name from object id and database id (sql2000)?

Question

I need the behaviour of sql2005 where function OBJECT_NAME takes two arguments obj id and db id, while sql2000 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.

Answer 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