OBJECT_ID of object in another database - how to find database ID or name/fully qualified object name?
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.
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 = ...
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