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 ways to start discussion:

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;

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.