A common requirement is to check if a database object exists for instance a table or view. This is often you used during deployment phase where new structure news to be applied and where we can simply drop the old object and create new one.
Version: I used SQL Server 2008 R2 to write the code. This should work with 2008 and 2005 versions (I am not sure about 2000)
How to do it? This is simple with SQL Servere Object_ID function. Each object has unique name (for each schema) and has unique number. Object_ID allows us to check the object number. The trick is if there is no number then it does not exist otherwise it exists and below is an example.
IF object_ID('dbo.tblName') is null
print 'on this line you put your drop statement for your table, view etc'
NOTE: If you use unicode characters in other words language specific characters like the word czesc (Hi in Polish) than you will have to make a small change to find table name that contains unicode characters otherwise it won't find it (See below, notice N before the single quote)
IF object_ID(N'dbo.tblczesc') is null
print 'on this line you put your drop statement for your table, view etc'
Check if table exists
Below I will show an real life example using tsql how to check if table exists and if yes then drop and create new one.
USE [MyDatabase]
IF OBJECT_ID('dbo.MyTableName') IS NOT NULL -- this statement check if given table name exists (is not null = exists)
DROP TABLE dbo.MyTableName -- if yes then drop
GO
CREATE TABLE dbo.MyTableName (ID INT)
Important: In this case I am deleting an object only when it exists in sql. Drop Table permanently deletes all data and this approach is usually used only for new tables or config tables that are populated with data using script after they are re-created. If you do not want to lose data make sure you write appropriate code (alter table).
