I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.
When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it?
Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?
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'mytablename', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res;
MySQL provides the simple
SHOW TABLES LIKE '%tablename%';
statement. I am looking for something similar.