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: I can't USE the other database from within an SP 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('Another…

Accepted Answer

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 = ...
```

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

Solution

Problem