Thursday, July 14, 2011

How to check SQL table exists or not, if exists delete the same and create a new one

Nowadays it became my habit that I am posting the code or article only when people ask the questions in forums.

This is one another regular question I used to see in forums "How to check SQL table exists or not, if exists delete the same and create a new one".

What normally people suggests is query the table and if nothing returned then it doesn't exists, but if you do that execution will take some time.
But if you check directly the Object ID of the table it will be much quicker.

Below is a simple query which helps you to check whether table exists or not in the database and if table exists then this query drops the table and create a new one with the same name.
IF OBJECT_ID('dbo.Users') IS NOT NULL
DROP TABLE dbo.Users 
GO
 
CREATE TABLE dbo.Users 
(
  ID INT,
  UserID Varchar(50)
) 
GO
 
select * from Users  
GO
I believe above code is self explanatory and you don’t need much explanation about that. Happy to provide more details if you have any questions.