OBJECT_ID of object in another database - how to find database ID or name/fully qualified object name?

Question

Example:

USE AnotherDB
-- This works - same ID as from other DB
SELECT OBJECT_ID('AnotherDB.ASchema.ATable')
-- This works
SELECT OBJECT_NAME(OBJECT_ID('AnotherDB.ASchema.ATable'))

USE ThisDB
-- This works - same ID as from other DB
SELECT OBJECT_ID('AnotherDB.ASchema.ATable')
-- Gives NULL
SELECT OBJECT_NAME(OBJECT_ID('AnotherDB.ASchema.ATable'))

Obviously the metadata functions expect a current database. The BOL entries typically have language like this for functions like OBJECT_NAME etc.:

The Microsoft SQL Server 2005 Database Engine assumes that object_id is in the context of the current database. A query that references an object_id in another database returns NULL or incorrect results.

The reasons I need to be able to do this:

1. I can't USE the other database from within an SP

2. I can't create a proxy UDF stub (or alter anything) in the other databases or in master (or any other database besides my own) to help me out.

So how can I get the database from OBJECT_ID('AnotherDB.ASchema.ATable') when I'm in ThisDB?

My goal is to take a possibly partially qualified name from a configuration table, resolving it in the current context to a fully qualified name, use PARSENAME to get the database name and then dynamic SQL to build a script to be able to get to the meta data tables directly with database.sys.* or USE db; sys.*

Answer 1

Do I understand it correctly that you want the db id of AnotherDB?

SELECT *
FROM    master..sysdatabases
WHERE   name = 'AnotherDB'

Otherwise, you can USE other db's in dynamic SQL if it helps:

DECLARE @SQL    NVARCHAR(MAX)
,   @objId  INT

SET @SQL = N'
    USE AnotherDB

    SELECT  @id = OBJECT_ID(''customer'')
'

EXEC SP_EXECUTESQL @SQL
    ,   N'@id INT OUTPUT'
    ,   @id = @objId OUTPUT

SELECT  @objId

OR Execute SP's in other dbs with:

EXEC AnotherDB.dbo.ProcedureName 
      @paramX = ...
,     @paramY = ...