For each object which is schema scoped, SQL server identifies that object using the object id. If queried for objects like trigger (which is not schema scoped) or the objects which are not there in sys.objects view, SQL server won’t return the object id.
To get a valid object id for the object, you can use
OBJECT_ID (parameter) function. Usually, parameter is [schema].[object name] and the optional parameter is object type.
- OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
- object_name' [ ,'object_type' ] )
Also, take a note that if user doesn’t have right to any of the objects, server returns NULL if queried using OBJECT_ID function. That also means, server would return NULL for in built objects.
You can find the more detailed object categories
here.
Let’s take an example of the OJBECT_ID to retrieve the database constraints.
- IF OBJECT_ID('dbo.[DF_Employee_dept]','D') IS NOT NULL
- PRINT 'DF_Employee_dept exists in the database.'
- ELSE
- PRINT 'DF_Employee_dept does not exist in the database.'
Output: DF_Employee_dept exists in the database.
Try printing the object id via - PRINT OBJECT_ID('dbo.[DF_Employee_dept]','D')
As you can see in the below table data, we have Default constraint named DF_Employee_dept,and upon querying the object, it returns the object id of the default constraint. If you have noticed, the second parameter is the character identifying the type of object. In this case, it is D for Default Constraints. You can find all those types in above mentioned link.
This applies to all the objects for which user has permission.
Another example would be PRINT OBJECT_ID ('dbo.View_1','V')
(Create a sample view and try printing the object id of the same).
You would find this function very useful while writing complex stored procedure – where querying sys tables multiple times may create issue in terms of performance where this function might be handy and also makes the code more readable.
Please let me know if you have any question regarding this.