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.*

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

Solution 2

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.

Solution 3

Take a look at the PARSENAME function in TSQL - will allow you to pull out any of the 4-part portions of a fully (or non-fully) qualified name. For the database in your example:

select parsename('AnotherDB.ASchema.ATable',3)

returns:

AnotherDB

select parsename('AnotherDB.ASchema.ATable',2)

returns:

ASchema

If non-fully qualified, you'll get null results if you ask for the portion of a name that isn't included in the string:

select parsename('ASchema.ATable',3)

returns:

NULL

Solution 4

I had the same issue but with OJBECT_SCHEMA_NAME as well. Following on from chadhoc's response using parsename works with OBJECT_NAME like:

DECLARE @OrigTableName NVARCHAR(MAX);

SELECT @OrigTableName = 'AnotherDB.ASchema.ATable'

SELECT OBJECT_NAME(OBJECT_ID(@OrigTableName), DB_ID(PARSENAME(@OrigTableName, 3)))
, OBJECT_SCHEMA_NAME(OBJECT_ID(@OrigTableName), DB_ID(PARSENAME(@OrigTableName, 3)))

View more solutions