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

You should be able to do this:

SELECT
   name
FROM
    AnotherDB.sys.objects   --changes context
WHERE
    object_id = OBJECT_ID('AnotherDB.ASchema.ATable')

This is what you effectively do with OBJECT_ID('AnotherDB.ASchema.ATable')

This means that you could rely on dbname.sys.objects and avoid confusion with metadata functions.

Note: the new Catalog views are designed to be used and not change from version to version, as per the link. In the old days, it was consider bad practice to use system tables but the stigma still remains. So, you can safely rely on sys.objects rather that the metadata functions.