Hi Everyone!

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statement.

When you Google for the answer, you get so many different answers. Is there an official/backward & forward compatible way of doing it?

Here are two possible ways of doing it. Which is the standard/best way of doing it?

First way:

IF EXISTS (SELECT 1 

           FROM INFORMATION_SCHEMA.TABLES 

           WHERE TABLE_TYPE='BASE TABLE' 

           AND TABLE_NAME='mytablename') 

   SELECT 1 AS res ELSE SELECT 0 AS res;

Second way:

IF OBJECT_ID (N'".$table_name."', N'U') IS NOT NULL 

   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides a nice SHOW TABLES LIKE '%tablename%'; statement. I am looking for something similar.

Advance Thanks!