What is OBJECT_ID in SQL Server?

If you ever looked at the dynamic management views or one of the system catalogs in SQL Server a column called Object ID will be associated, from the looks of it this number doesn’t actually relate to anything – but there is actually a lot more to it than that. Object_ID is a unique id number for an object within the database, this is used internally by SQL Server.  It should be noted, not all objects have an object_id.  DDL triggers for example do not as they are not schema-scoped.

If we run a query against sys.columns, lots of information will be returned about all the columns in the database.

SELECT * FROM sys.columns

However, the result set will look something like this:

object_id | name | column_id ….
4    rowsetid    1
4    rowsetcolid    2
…
151671588    MiddleName    4

While the column name is in readable text, the object_id isn’t actually anything useful (to a human).  In fact, this is the ID of the table which the column belongs to. In order to get the name of the object from the object_id there is a function called Object_Name(). This will take the object_id and return a readable name. If we re-run the query but including the object_name

SELECT OBJECT_NAME(object_id) as TableName, * FROM sys.columns

Then the table name is returned in a helpful manner.

TableName | object_id | name | column_id ….
sysrowsetcolumns    4    rowsetid    1
sysrowsetcolumns    4    rowsetcolid    2
vEmployee    151671588    MiddleName    4

We could also use the Object_ID() function to obtain the object_id for an object in database.  For example, the query below asks for the object_id for the Customers table in Northwind.  This returns 21575115 

USE Northwind
GO
SELECT OBJECT_ID(‘Customers’)

Of course, flipping this around will return us Customers again.

USE Northwind
GO
SELECT OBJECT_NAME(21575115)

There is also a similar function when you are given a schema_id. Schema_Name() works in the same fashion but returns the schema name based on the Schema_Id.

SELECT OBJECT_NAME(object_id), SCHEMA_NAME(schema_id), * FROM sys.tables

Hopefully this will clear up what Object_ID is. I know next time I query one of SQL Server tables I will be able to return at least some more meaningful information.