另一个数据库中对象的OBJECT_ID - 如何查找数据库ID或名称/完全限定对象名称？

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

显然，元数据函数需要当前的数据库。对于OBJECT_NAME等功能，BOL条目通常具有这样的语言：

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.

Microsoft SQL Server 2005数据库引擎假定object_id位于当前数据库的上下文中。引用另一个数据库中的object_id的查询返回NULL或不正确的结果。

The reasons I need to be able to do this:

我需要能够做到这一点的原因：

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

   我无法在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.

   我不能在其他数据库或master（或我自己的任何其他数据库）中创建代理UDF存根（或更改任何东西）来帮助我。

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

那么当我在ThisDB中时，如何从OBJECT_ID（'AnotherDB.ASchema.ATable'）获取数据库？

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

我的目标是从配置表中获取一个可能部分限定的名称，在当前上下文中将其解析为完全限定名称，使用PARSENAME获取数据库名称，然后使用动态SQL构建脚本以便能够访问元数据数据表直接与database.sys。*或USE db; SYS。*

4 个解决方案

#1

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

我是否正确理解你想要AnotherDB的db id？

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

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

否则，如果有帮助，您可以在动态SQL中使用其他数据库：

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:

或者在其他dbs中执行SP：

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

#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')

这是你用OBJECT_ID（'AnotherDB.ASchema.ATable'）有效地做的事情

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

这意味着您可以依赖dbname.sys.objects并避免与元数据函数混淆。

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.

注意：新的目录视图旨在使用，而不是根据链接从版本更改为版本。在过去，使用系统表被认为是不好的做法，但耻辱仍然存在。因此，您可以安全地依赖sys.objects而不是元数据功能。

#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:

查看TSQL中的PARSENAME函数 - 将允许您拉出完全（或非完全）限定名称的任何4部分部分。对于示例中的数据库：

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

returns:

收益：

AnotherDB

AnotherDB

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

returns:

收益：

ASchema

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:

如果非完全限定，如果要求字符串中未包含的名称部分，则会得到null结果：

select parsename('ASchema.ATable',3)

returns:

收益：

NULL

空值

#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:

我有同样的问题，但也有OJBECT_SCHEMA_NAME。继chadhoc的回复使用parsename与OBJECT_NAME一起工作，如：

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)))