Verify existence of SQL tables using Object_ID()

SQL-Tables
SQL-Tables

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

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. 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 ?

    Post a Reply

Submit a Comment

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

More from CodeSpread:

  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • SQL: Common Table Expression called CTE SlideShow and ScreenCastSQL: Common Table Expression called CTE SlideShow and ScreenCastHere is a slide show and  screencast of Common Table Expression called CTE . This slide show is also available for download. You can also check the screencast on our youtube channel. http...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...