Applies To: SQL Server 2014, SQL Server 2016 Preview
Returns the database object identification number of a schema-scoped object.
Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.
- ' object_name '
Is the object to be used. object_name is either varchar or nvarchar. If object_name is varchar, it is implicitly converted to nvarchar. Specifying the database and schema names is optional.
- ' object_type '
Is the schema-scoped object type. object_type is either varchar or nvarchar. If object_type is varchar, it is implicitly converted to nvarchar. For a list of object types, see the type column in sys.objects (Transact-SQL).
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.
When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: SELECT OBJECT_ID('tempdb..#mytemptable').
The following example returns the object ID for the Production.WorkOrder table in the AdventureWorks2012 database.
USE master; GO SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID'; GO
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. 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
The following example returns information for all indexes and partitions of the Person.Address table in the AdventureWorks2012 database by using the sys.dm_db_index_operational_stats function.
When you are using the Transact-SQL functions DB_ID and OBJECT_ID to return a parameter value, always make sure that a valid ID is returned. 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. 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