Verify existence of SQL tables using Object_ID()

Welcome to CodeSpread!

Why Object_ID() ?

Let’s create a SQL table “Employee”

CREATE TABLE Employee
(
[EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY,

[EmployeeName] [varchar](50) NULL,

[Salary] [int] NULL,
)

Looks ok!!
But did I check whether the same table already exists in database? Good Question!
Let’s check then, by verifying that the table has an object ID.

IF OBJECT_ID(N‘Employee’) IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
[EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY,

[EmployeeName] [varchar](50) NULL,

[Salary] [int] NULL,
)

In the above example, if the object id is available for the table then it is dropped else the drop statement is jumped over.

What is Object_ID()?

Returns the database object identification number of a schema-scoped object.

What is database object identification number?

The OBJECT_ID is used to identify the object uniquely in the system base tables. It is the primary key of the sys.sysschobjs base table used by the sys.objects metadata view and appears in many other of the metadata views. e.g sys.partitions.

What is a schema scoped object?

A schema scoped object is one that belongs to a schema (e.g a table, view, stored procedure). Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID.

Non schema scoped objects metadata is still stored in sys.sysschobjs but does not show up in the sys.objects view.
To obtain the object identification numbers for them, query the appropriate catalog view.


SELECT OBJECT_ID FROM sys.triggers WHERE name = ‘DatabaseTriggerLog’.

What about temporary tables?

When specifying a temporary table name, the database name must precede the temporary table name, for example:

SELECT OBJECT_ID('tempdb..#mytemptable')

Just adding two extra lines of code can save lot of efforts.

Ref:
• http://technet.microsoft.com/en-us/library/aa276843%28v=sql.80%29.aspx
• http://stackoverflow.com/questions/9372867/what-does-object-id-do-in-sql-server
• http://technet.microsoft.com/en-us/library/ms190328.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?