Testing for object existence

[NathanGriffiths]

I have traditionally tested for the existence of objects such as tables using the IF EXISTS syntax and information_schema e.g.

IF EXISTS(SELECT 1 FROM Information_Schema.Tables WHERE Table_Name = 'MyTable')
   BEGIN
...
   END

But I think this might be a simpler way;

IF Object_ID('MyTable') IS NOT NULL
   BEGIN
...
   END

Any comments?

thanks,

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[nigelrivett]

That should be ok as long as you don't mix names across tables and SPs etc.

Why are you checking objects? If it's for creation scripts I usually follow whatever the scripting feature does - makes it easier to check scripts against what's there.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NathanGriffiths]

The system I am working on involves a lot of importing and exporting data between databases and a lot of development work on tables, the existence check is usually for error checking in case the target table is missing (it happens!).

cheers

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------