OBJECT_NAME (Transact-SQL)OBJECT_NAME (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

返回架构范围内对象的数据库对象名称。Returns the database object name for schema-scoped objects. 有关架构范围内对象的列表,请参阅 sys.objects (Transact-SQL)For a list of schema-scoped objects, see sys.objects (Transact-SQL).

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

OBJECT_NAME ( object_id [, database_id ] )  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

object_idobject_id
要使用的对象的 ID。Is the ID of the object to be used. object_id 的数据类型为 int,并假定为指定数据库或当前数据库上下文中的架构范围内的对象。object_id is int and is assumed to be a schema-scoped object in the specified database, or in the current database context.

database_iddatabase_id
要在其中查找对象的数据库的 ID。Is the ID of the database where the object is to be looked up. database_id 的数据类型为 int。database_id is int.

返回类型Return Types

sysnamesysname

例外Exceptions

出现错误时或调用方没有查看对象的权限时,将返回 NULL。Returns NULL on error or if a caller does not have permission to view the object. 如果目标数据库的 AUTO_CLOSE 选项设置为 ON,则此函数将打开此数据库。If the target database has the AUTO_CLOSE option set to ON, the function will open the database.

用户只能查看符合如下条件的安全对象的元数据:该安全对象为该用户所有,或已授予该用户对该安全对象的权限。A user can only view the metadata of securables that the user owns or on which the user has been granted permission. 也就是说,如果用户对该对象没有任何权限,则那些会生成元数据的内置函数(如 OBJECT_NAME)可能返回 NULL。This means that metadata-emitting, built-in functions such as OBJECT_NAME may return NULL if the user does not have any permission on the object. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

权限Permissions

需要对对象拥有 ANY 权限。Requires ANY permission on the object. 若要指定数据库 ID,还需要对数据库拥有 CONNECT 权限,或者必须启用 guest 帐户。To specify a database ID, CONNECT permission to the database is also required, or the guest account must be enabled.

备注Remarks

系统函数可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。System functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed. 有关详细信息,请参阅表达式WHEREFor more information, see Expressions and WHERE.

由此系统函数返回的值使用当前数据库的排序规则。The value returned by this system function uses the collation of the current database.

默认情况下,SQL Server 数据库引擎SQL Server Database Engine假定 object_id 在当前数据库的上下文中。By default, the SQL Server 数据库引擎SQL Server Database Engine assumes that object_id is in the context of the current database. 在其他数据库中引用 object_id 的查询将返回 NULL 或错误的结果。A query that references an object_id in another database returns NULL or incorrect results. 例如,以下查询中当前数据库上下文是 AdventureWorks2012AdventureWorks2012For example, in the following query the context of the current database is AdventureWorks2012AdventureWorks2012. 数据库引擎Database Engine将尝试返回在该数据库(而非查询的 FROM 子句中指定的数据库)中指定的对象 ID 的对象名称。The 数据库引擎Database Engine tries to return an object name for the specified object ID in that database instead of the database specified in the FROM clause of the query. 因此,会返回不正确的信息。Therefore, incorrect information is returned.

USE AdventureWorks2012;  
GO  
SELECT DISTINCT OBJECT_NAME(object_id)  
FROM master.sys.objects;  
GO  

您可以通过指定数据库 ID 在其他数据库的上下文中解析对象名。You can resolve object names in the context of another database by specifying a database ID. 以下示例在 master 函数中指定 OBJECT_SCHEMA_NAME 数据库的数据库 ID,并返回正确的结果。The following example specifies the database ID for the master database in the OBJECT_SCHEMA_NAME function and returns the correct results.

USE AdventureWorks2012;  
GO  
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, 1) AS schema_name  
FROM master.sys.objects;  
GO  

示例Examples

A.A. 在 WHERE 子句中使用 OBJECT_NAMEUsing OBJECT_NAME in a WHERE clause

以下示例将返回来自对象的 sys.objects 目录视图的列,该对象是通过 OBJECT_NAMEWHERE 语句的 SELECT 子句中指定的。The following example returns columns from the sys.objects catalog view for the object specified by OBJECT_NAME in the WHERE clause of the SELECT statement.

USE AdventureWorks2012;  
GO  
DECLARE @MyID INT;  
SET @MyID = (SELECT OBJECT_ID('AdventureWorks2012.Production.Product',  
    'U'));  
SELECT name, object_id, type_desc  
FROM sys.objects  
WHERE name = OBJECT_NAME(@MyID);  
GO  

B.B. 返回对象架构名称和对象名称Returning the object schema name and object name

以下示例返回所有缓存查询计划(为非临时语句或预定义语句)的对象架构名称、对象名称和 SQL 文本。The following example returns the object schema name, object name, and SQL text for all cached query plans that are not ad hoc or prepared statements.

SELECT DB_NAME(st.dbid) AS database_name,   
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,  
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,   
    st.text AS query_text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
WHERE st.objectid IS NOT NULL;  
GO  

C.C. 返回由三部分组成的对象名称Returning three-part object names

以下示例返回所有数据库中所有对象的数据库名称、架构名称和对象名称,同时返回 sys.dm_db_index_operational_stats 动态管理视图中的其他所有列。The following example returns the database, schema, and object name along with all other columns in the sys.dm_db_index_operational_stats dynamic management view for all objects in all databases.

SELECT QUOTENAME(DB_NAME(database_id))   
    + N'.'   
    + QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))   
    + N'.'   
    + QUOTENAME(OBJECT_NAME(object_id, database_id))  
    , *   
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);  
GO  

示例:Azure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics and 并行数据仓库Parallel Data Warehouse

D.D. 在 WHERE 子句中使用 OBJECT_NAMEUsing OBJECT_NAME in a WHERE clause

以下示例将返回来自对象的 sys.objects 目录视图的列,该对象是通过 OBJECT_NAMEWHERE 语句的 SELECT 子句中指定的。The following example returns columns from the sys.objects catalog view for the object specified by OBJECT_NAME in the WHERE clause of the SELECT statement. (对象编号(在下面的示例中为 274100017)各不相同。(Your object number (274100017 in the example below) will be different. 若要测试此示例,请通过在数据库中执行 SELECT name, object_id FROM sys.objects; 来查看有效的对象编号。)To test this example, look up a valid object number by executing SELECT name, object_id FROM sys.objects; in your database.)

SELECT name, object_id, type_desc  
FROM sys.objects  
WHERE name = OBJECT_NAME(274100017);  

另请参阅See Also

元数据函数 (Transact-SQL) Metadata Functions (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
OBJECT_ID (Transact-SQL)OBJECT_ID (Transact-SQL)