OBJECT_ID (Transact-SQL)OBJECT_ID (Transact-SQL)

本主題適用於: 是SQL Server (從 2008 開始)是Azure SQL Database是Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

傳回結構描述範圍的物件之資料庫物件識別碼。Returns the database object identification number of a schema-scoped object.

重要

範圍不是結構描述的物件 (例如 DDL 觸發程序) 無法利用 OBJECT_ID 進行查詢。Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. 中找不到的物件sys.objects目錄檢視,請查詢適當的目錄檢視來取得物件識別碼。For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. 例如,若要傳回 DDL 觸發程序的物件識別碼,請使用SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog``'For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog``'.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]   
  object_name' [ ,'object_type' ] )  

引數Arguments

' object_name '' object_name '
這是要使用的物件。Is the object to be used. object_namevarcharnvarcharobject_name is either varchar or nvarchar. 如果object_namevarchar,隱含地轉換成nvarcharIf object_name is varchar, it is implicitly converted to nvarchar. 資料庫和結構描述名稱的指定是選擇性的。Specifying the database and schema names is optional.

' object_type '' object_type '
這是結構描述範圍物件類型。Is the schema-scoped object type. object_typevarcharnvarcharobject_type is either varchar or nvarchar. 如果object_typevarchar,隱含地轉換成nvarcharIf object_type is varchar, it is implicitly converted to nvarchar. 如需物件類型的清單,請參閱類型中的資料行sys.objects (TRANSACT-SQL ).For a list of object types, see the type column in sys.objects (Transact-SQL).

傳回類型Return Types

intint

例外狀況Exceptions

若為空間索引,OBJECT_ID 會傳回 NULL。For a spatial index, OBJECT_ID returns NULL.

發生錯誤時傳回 NULL。Returns NULL on error.

使用者只能檢視使用者擁有或被授與某些權限之安全性實體的中繼資料。A user can only view the metadata of securables that the user owns or on which the user has been granted permission. 這表示發出中繼資料的內建函數 (例如,OBJECT_ID) 會在使用者不具有該物件任何權限時傳回 NULL。This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object. 如需相關資訊,請參閱 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

備註Remarks

當系統函數的參數是選擇性時,就會假設使用目前資料庫、主機電腦、伺服器使用者或資料庫使用者。When the parameter to a system function is optional, the current database, host computer, server user, or database user is assumed. 內建函數後面一律必須接著括號。Built-in functions must always be followed by parentheses.

當指定的暫存資料表名稱時,資料庫名稱必須在前面的暫存資料表名稱,除非在目前資料庫是tempdbWhen a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. 例如: SELECT OBJECT_ID('tempdb..#mytemptable')For example: SELECT OBJECT_ID('tempdb..#mytemptable').

系統函數可以用於選取清單、WHERE 子句以及任何可以使用運算式的位置。System functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed. 如需詳細資訊,請參閱運算式 (TRANSACT-SQL )其中 (TRANSACT-SQL ).For more information, see Expressions (Transact-SQL) and WHERE (Transact-SQL).

範例Examples

A.A. 傳回指定物件的物件識別碼Returning the object ID for a specified object

下列範例會傳回 AdventureWorks2012AdventureWorks2012資料庫之 Production.WorkOrder 資料表的物件識別碼。The following example returns the object ID for the Production.WorkOrder table in the AdventureWorks2012AdventureWorks2012 database.

USE master;  
GO  
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';  
GO  

B.B. 確認物件存在Verifying that an object exists

下列範例會確認資料表有物件識別碼,來檢查指定的資料表是否存在。The following example checks for the existence of a specified table by verifying that the table has an object ID. 如果資料表存在,就會刪除它。If the table exists, it is deleted. 如果資料表不存在,就不會執行 DROP TABLE 陳述式。If the table does not exist, the DROP TABLE statement is not executed.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL  
DROP TABLE dbo.AWBuildVersion;  
GO  

C.C. 使用 OBJECT_ID 來指定系統函數參數的值Using OBJECT_ID to specify the value of a system function parameter

下列範例會傳回所有索引和資料分割的相關資訊Person.Address資料表中 AdventureWorks2012AdventureWorks2012資料庫使用sys.dm_db_index_operational_stats函式。The following example returns information for all indexes and partitions of the Person.Address table in the AdventureWorks2012AdventureWorks2012 database by using the sys.dm_db_index_operational_stats function.

重要

當您使用 Transact-SQLTransact-SQL 函數 DB_ID 和 OBJECT_ID 傳回參數值時,請務必確定所傳回的是有效的識別碼。When you are using the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to return a parameter value, always make sure that a valid ID is returned. 如果找不到資料庫或物件名稱 (例如,因為不存在或是拼錯了),這兩個函數都會傳回 NULL。If the database or object name cannot be found, such as when they do not exist or are spelled incorrectly, both functions will return NULL. Sys.dm_db_index_operational_stats函式會將 NULL 解譯為萬用字元值,指定所有資料庫或所有物件。The sys.dm_db_index_operational_stats function interprets NULL as a wildcard value that specifies all databases or all objects. 由於這不見得是刻意安排的作業,因此本節所舉的範例,只會示範決定資料庫和物件識別碼的安全方法。Because this can be an unintentional operation, the examples in this section demonstrate the safe way to determine database and object IDs.

DECLARE @db_id int;  
DECLARE @object_id int;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');  
IF @db_id IS NULL   
  BEGIN;  
    PRINT N'Invalid database';  
  END;  
ELSE IF @object_id IS NULL  
  BEGIN;  
    PRINT N'Invalid object';  
  END;  
ELSE  
  BEGIN;  
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);  
  END;  
GO  

範例: Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

D: 傳回指定之物件的物件識別碼D: Returning the object ID for a specified object

下列範例會傳回 AdventureWorksPDW2012AdventureWorksPDW2012資料庫之 FactFinance 資料表的物件識別碼。The following example returns the object ID for the FactFinance table in the AdventureWorksPDW2012AdventureWorksPDW2012 database.

SELECT OBJECT_ID(AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';  

請參閱<See Also

中繼資料函數 (TRANSACT-SQL ) Metadata Functions (Transact-SQL)
sys.objects (TRANSACT-SQL ) sys.objects (Transact-SQL)
sys.dm_db_index_operational_stats (TRANSACT-SQL ) sys.dm_db_index_operational_stats (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
OBJECT_NAME (TRANSACT-SQL )OBJECT_NAME (Transact-SQL)