SQL Server 2008 – 2 Ways To Get Object Id Of A Database Object

Every object in SQL Server database has an Object Id which is used extensively by SQL Server for most operations. There are times when we would like to know what the object ID is for a particular object. In this example I will show you two ways to retrieve Object Id. My examples will retrieve Object Id for HumanResources.Employee table in AdventureWorks database.

Method 1

First method is to query sys.objects system view and pass in the name of object and also the name of schema.

SELECT OBJECT_ID FROM sys.objects
WHERE name = 'Employee'
AND SCHEMA_ID =
    (SELECT SCHEMA_ID
    FROM sys.schemas
    WHERE name = 'HumanResources')

This query returns the Object Id shown below.

Method 2

While the first approach works, there is a better way to get the Object ID and that is to use the OBJECT_ID function. Here is another query which fetches the Object ID for HumanResources.Employee table in AdventureWorkds database.

SELECT OBJECT_ID(N'HumanResources.Employee')

And as expected I get my result.

Both approaches are valid. However there are subtle differences. For example method 1 returns the result set in which the column is called ‘OBJECT_ID’, method 2 does not name the column. Another difference is that if you mistype the object name them method 1 will return zero rows while method 2 will simply return a NULL value.